Measure that distinct counts based on other columns

[Anonymous]

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?

[Anonymous]

@TomMartens 
@simrantuli 

@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?

[simrantuli]

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 

[Ashish_Mathur]

Hi,

Try this measure

=calculate(distinctcount(Data[ID]),Data[Class]="H"||Data[Class]="K"||Data[Class]="R1")

Hope this helps.

[TomMartens]

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

[Ashish_Mathur]

Hi,

I gave another answer to let the user know that there is another option.

[TomMartens]

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

[Ashish_Mathur]

Thank you for sharing that.

[harshnathani]

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)

[TomMartens]

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

[simrantuli]

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!

[simrantuli]

Hi @Anonymous ,

Create the below measure.

You should get the desired output.

Best Regards

Simran Tuli

[TomMartens · Accepted 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