Forum Discussion
Distinct Count with multiple conditions
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")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 amountCalculate(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.
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!!
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.
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!!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).
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")