Forum Discussion
Distinct Count with multiple conditions
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!
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.
11 Replies
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,
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")