Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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")
You are close
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,
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?
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")
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!!
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!!
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.
You are close
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!!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |