Distinct Count with multiple conditions

[KAmorris]

Hi! 

I'm wondering if someone could please help with a suggestion for two (related) issues I'm having.

Issue 1: 
I'd like to create a measure that counts the number of distinct responses in a table ('Table'(Response_ID)] , but returns different values depending on certain criteria.

Specifically, I'd like to have the following: 

- If the distinct count of Response_ID column is equal to or greater than 15, I want to the count itself to be displayed (e.g., "25")

- If the distinct count is 0, then I want it to show up as "0"

- If the distinct count is between 1 and 14, I want it to show up as "<15"

I had been using the measure:
ResponseIDCount = (Calculate(if(DISTINCTCOUNT('Table'[Response_ID])>=15, DISTINCTCOUNT ('Table'[Response_ID]), "<15") 

But this doesn't account for when the distinct count is 0. 

Issue 2:

I'd also like to use the measure created to solve Issue 1 in another measure. So, for example, I'm trying to count the number of users ('Table'[Response_ID]) who had a particular response to another column ('Table'[Column]). Responses were to a simple yes/no question, so I transformed it so that a yes = 1, and a no was a blank (null).

What I'd like is to have a measure that essentially counts the number of distinct responses who said yes. But this measure should only return the following:

- the count if the number of responses is greater than or equal to 15.

- If the number of responses is 0, then return the number '0'.

- If the number of responses is between 1 and 14, then return "<15". 

I had been using the measure: CALCULATE (DISTINCTCOUNT('Table'[Response_ID]), 'Table'[Column] =1)

But this doesn't account for the nuances as outlined above (e.g., if the # of responses <15 or 0). 

Any refinements/suggestions would be most appreciated!! 

Thanks!

[KAmorris · Accepted solution]

I think I figured it out - I just had to switch the order of the last two statements, so the following seems to work:

ResponseIDCount = SWITCH(
TRUE(),
DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID], DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0,
DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15")

[rsbin]

@KAmorris ,

The SWITCH function is similar to a nested IF statement.  Please try this:

ResponseIDCount = SWITCH(
                      TRUE(),
           DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID] ),
           DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15",
           DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0 )

Regards,

[KAmorris]

Thanks so much @rsbin . Your suggestion seems to mostly work, but for some reason the count when the distinct count of 'Table'[ResponseID] should be 0 still shows up as "<15". Any other ideas or things I should check for?

[KAmorris · Accepted solution]

I think I figured it out - I just had to switch the order of the last two statements, so the following seems to work:

ResponseIDCount = SWITCH(
TRUE(),
DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID], DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0,
DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15")

[rsbin]

@KAmorris ,

Glad you were able to get it to work for you.

Regards,

[KAmorris]

If you don't mind, I have one follow up question @rsbin.  Is there a way to use a similar solution to solve this issue: 

I'm trying to count the number of users ('Table'[Response_ID]) who had a particular response to another column ('Table'[Column]). Responses were to a simple yes/no question, so I transformed it so that a yes = 1, and a no was a blank (null).

What I'd like is to have a measure that essentially counts the number of distinct responses who said yes. But this measure should only return the following:

- the count if the number of responses is greater than or equal to 15.

- If the number of responses is 0, then return the number '0'.

- If the number of responses is between 1 and 14, then return "<15". 

I had been using the measure: CALCULATE (DISTINCTCOUNT('Table'[Response_ID]), 'Table'[Column] =1)

But this doesn't account for the nuances as outlined above (e.g., if the # of responses <15 or 0). 

Would a similar SWITCH statement be useful here? I'm trying to play around with it but haven't managed to make it work yet... 

Thanks so very much for all of your help!!

[rsbin]

@KAmorris ,

Please post a small sample of data.  Paste it in as a table not as an image, but exclude any sensitive data.  Enables folks to easily use that data to come up with a workable solution.

[KAmorris]

Thanks for the suggestion @rsbin.

Here's some example data: 

Response_ID	Disability	MoreThanOneDisability
A	Condition 1	1
B	null	0
C	Condition 2	1
D	Condition 1	1

What I would like to do is include a Card on the PowerBI report that shows the number of responses that have a '1' under "MoreThanOneDisability" column. 

So that if there's between 1 and 14 responses that have a '1' under 'MoreThanOneDisability' column, it shows up as "<15". But if there's 15+ responses, then the actual count shows up. And if there aren't any responses at all, it shows up as 0.

Please let me know if any other information would be helpful - still learning and really appreciate this community's willingness to help!!

[rsbin]

@KAmorris ,

Please post a small sample of data.  Paste it in as a table not as an image, but exclude any sensitive data.  Enables folks to easily use that data to come up with a workable solution.

Edit:  I re-read your question above a couple of times.

Yes, the SWITCH would work as well.

Instead of:

DISTINCTCOUNT( 'Table'[ResponseID] ) in the Switch statement

use your Measure above:

CALCULATE (DISTINCTCOUNT('Table'[Response_ID]), 'Table'[Column] =1)

Replace [Column] with [MoreThanOneDisability].

I noticed when you pasted your data, there were extra spaces.  Be sure your Column Names are clean (no extra spaces before or after).

[KAmorris]

Sorry about the extra spaces - I don't have extra spaces in my column names in my data, but good reminder! 

I've tried to replace as you suggested, but not sure if I correctly understand. Should it be something like the following? If so, the result I get is quite high compared to what it should be.

SWITCH(

TRUE(),

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) >=15, DISTINCTCOUNT(Table[Response_ID]),

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) =0,0,

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) <15, "<15")

[rsbin · Accepted solution]

@KAmorris ,

You are close

SWITCH(

TRUE(),

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) >=15, DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) 
// If Calculation is >= 15, then use the Calculation amount

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) =0,0,
// If Calculation = 0, then 0.

Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) <15, "<15")
// If Calculation < 15, then use "<15".   In Dax you can use "//" to make comments to yourself or others.

Hope this added clarification helps.

[KAmorris]

Yes! That's it!! Thank you so so much @rsbin. This has been a huge struggle for me and I really appreciate your willingness to work it through with me. Thank you!!