Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, everyone!
My table follows this structure:
ID | Class | CQ |
111 | T | 00.2 |
111 | T | 00.2 |
111 | K | 00.2 |
222 | P | 00.2 |
222 | L8 | 00.2 |
333 | R1 | 00.2 |
444 | H | 00.1 |
444 | K | 00.1 |
As you can see, the values in "ID" column can have more than one occurence for each ID (can be repeated). I want to create a measure that counts all the following DISTINCT IDs values:
If Class = H or K or R1
So if a certain ID has at least one occurrence in Class column thats = H or K or R1 the measure will count it.
For the example table above, the count measure should show me 3, cause theres 3 IDs in the table that had a least 1 ocurrence of H, K or R1 in the Class column (111, 333 and 444)
Can someone help me?
Solved! Go to Solution.
Hey @Anonymous ,
this measure
Measure 2 =
/* H, K, R1 */
CALCULATE(
DISTINCTCOUNT('Table (3)'[ID ])
, 'Table (3)'[Class] in {"H" , "K" , "R1"}
)
retruns 3.
Hopefully, this is what you are looking for.
Regards,
Tom
@harshnathani
@Ashish_Mathur
Thank you all! all measures worked perfeclty!
Just a little question before closing the topic: If I wanted to make a specific condition for a Class type, based on the CQ column? Example: if I wanted the measure to count all values with Class = H or K or R1, but with a special condition for values R1: that they would be counted ONLY if their CQ column was NOT equal to 00.2.
Based on the example table above, the measure should show me the value 2 (ID 111 and ID 444). It would no longer count ID 333 because its CQ column = 00.2. If this column were any value other than 00.2, ID 333 would be counted as well. How do I do that?
Hi @Anonymous ,
For your 2nd requirement, try the below measure.
Col = CALCULATE(DISTINCTCOUNT('Table'[ID ]),FILTER('Table','Table'[Class] IN {"H","K"} || ('Table'[Class]="R1" && 'Table'[CQ] <> 0.2)))
Best Regards
Simran Tuli
Hi,
Try this measure
=calculate(distinctcount(Data[ID]),Data[Class]="H"||Data[Class]="K"||Data[Class]="R1")
Hope this helps.
Hey @Ashish_Mathur ,
I'm wondering if you see any benefits in using || (or) in comparison to the IN operator.
If not, do you think you can explain why you just added another answer? Don't hesitate to DM me, as I'm curious.
Regards,
Tom
Hi,
I gave another answer to let the user know that there is another option.
Hey @Ashish_Mathur ,
I see, makes sense.
But then you should also mention that multiple OR statement lead to a more DAX complex execution plan. The more complex the execution plan, the slower the statement (see here: https://www.sqlbi.com/articles/the-in-operator-in-dax/)
Personally I appreciate having different opportunities, but then I also want to know the price I have to pay, here: multiple OR are slower than IN.
Regards,
Tom
Thank you for sharing that.
Hi @Anonymous ,
Try this measure
Measure = CALCULATE(DISTINCTCOUNT('Table'[ID ]), FILTER('Table','Table'[Class] IN {"T","K","R1"}))
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hey @harshnathani ,
I'm wondering you see any benefit by introducing the FILTER function inside the measure in comparison to the measure I suggested.
Hey @simrantuli , do I miss something or is your proposed measure a copy & paste version of the measure I suggested as an solution, and then just changing the table name?
Regards,
Tom
Hi @TomMartens,
My answer is NOT a copy and paste version of your measure.
When I was trying this scenario on Power BI Desktop on my laptop (as you can see from the images that I tried it myself first before answering), there wasn't a single answer posted to this thread. When I typed my answer and hit 'Submit' button, I saw your answer on top of mine with the exact same measure as mine.
So, don't get offended. I didn't copy your measure. There aren't a lot of solutions to this scenario and it can happen that 2 or more people suggest the same solution 🙂
Cheers!
Hi @Anonymous ,
Create the below measure.
You should get the desired output.
Best Regards
Simran Tuli
Hey @Anonymous ,
this measure
Measure 2 =
/* H, K, R1 */
CALCULATE(
DISTINCTCOUNT('Table (3)'[ID ])
, 'Table (3)'[Class] in {"H" , "K" , "R1"}
)
retruns 3.
Hopefully, this is what you are looking for.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |