The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have two table with the following relevant fields:
- CS
* Name
* Related Case
- SC
* Child Case Ref
An example table would be
Name Related Case Child Case Ref
Sean Case1 Case1a
Sean Case 2 Case2a
Sean Case 3 Case3a
David Case 4 Case4a
What I want is to add a column that would show the number of distinct 'Related Case' values per name.
So for Sean that would be 3 and david 1.
Name Related Case Child Case Ref NEW COLUMN
Sean Case1 Case1a 3
Sean Case 2 Case2a 3
Sean Case 3 Case3a 3
David Case 4 Case4a 1
How can I do this?
Solved! Go to Solution.
Hi @SPa
Thanks for the reply from @Greg_Deckler and @bhanu_gautam .
@SPa , you can try the following measure.
Measure = COUNTX(FILTER(ALL('Table'), [Name] = MAX([Name])), [Related Case])
Output:
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ah ok, thank you for explaining. My issue is that I have a directquery connection to the data. Therefore a new column with calculate is not possible.
Hi @SPa
Thanks for the reply from @Greg_Deckler and @bhanu_gautam .
@SPa , you can try the following measure.
Measure = COUNTX(FILTER(ALL('Table'), [Name] = MAX([Name])), [Related Case])
Output:
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@SPa, You can achieve this using DAX and since you have not mentioned relation between table 1 and 2 , I have not considered it and it won't impact the outcome
Please find the attached PBIX
Proud to be a Super User! | |
When I try this, I get the following result:
Name Related Case Child Case Ref NEW COLUMN
Sean Case1 Case1a 1
Sean Case 2 Case2a 1
Sean Case 3 Case3a 1
David Case 4 Case4a 1
@SPa Try:
Measure =
VAR __Name = MAX( 'Table'[Name] )
VAR __Table = FILTER( ALL( 'Table' ), [Name] = __Name )
VAR __Result = COUNTROWS( 'Table' )
RETURN
__Result
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
104 | |
76 | |
73 | |
47 |
User | Count |
---|---|
169 | |
112 | |
110 | |
82 | |
75 |