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.
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")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.