The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a questionnaire that asks multpul questions. The questions are related to set categories. Clients do not have to answer all questions, so I filter out questions not answered.
What I am trying to achive is to get a distinct count of each catagory, for each client who answered the questionnaire.
Therefore if Category 1 has 3 questions and the client answers 2 of the 3 questions I only want to count this Category Once.
Data example
Client Number | Question ID | Question Text | Question Category | Date |
1 | 1 | Healthy Living | 01/01/20 | |
1 | 2 | Healthy Living | 01/01/20 | |
1 | 3 | Exercise | 01/01/20 | |
1 | 4 | Food | 01/01/20 | |
1 | 5 | Food | 01/01/20 | |
2 | 1 | Healthy Living | 02/01/20 | |
2 | 3 | Exercise | 02/01/20 | |
3 | 2 | Healthy Living | 05/01/20 | |
3 | 4 | Food | 05/01/20 | |
4 | 1 | Healthy Living | 10/01/20 | |
4 | 4 | Food | 10/01/20 | |
4 | 5 | Food | 10/01/20 |
The desired out put would be
Health Living = 4
Exersize = 2
Food = 3
I am using a measure so that I can put this into a Table visual that will filter on date
I am nearly there with the following code, but it counts each question and not the category!
Category Count =
CALCULATE(
DISTINCTCOUNTNOBLANK( 'Support Need'[Client Number] ),
FILTER(
'Support Need' ,
CONTAINSSTRING( 'Support Need'[Question Category] , SELECTEDVALUE( 'Support Need References (STATIC)'[Question Category] )
)
)
)
Any pointers to help me get over the last hurdle would be much apprectiated.
Solved! Go to Solution.
Hi @DemoFour
1. Place Question category in a table visual
2. Place this measure in the visual
Measure =
COUNTROWS( DISTINCT( Table1[Client Number] ) )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I think here's what you're looking for:
achieved using this measure:
Distinctcount by categories =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'DataTable'[Client Number] ),
ALLEXCEPT ( 'DataTable', 'DataTable'[Question Category] )
)
)
Proud to be a Super User!
Paul on Linkedin.
Thanks for your suggestion, this also works and is a variation on my other attempt below.
Just goes to show that DAX can be as complicated or as simple as we make it!
Thank you for your help @PaulDBrown
S3 Count y =
CALCULATE(
DISTINCTCOUNT( 'Support Needs'[Client Number] ),
ALLEXCEPT(
'Support Needs' ,
'Support Needs'[Question Category],
'Support Needs'[Client Number]
)
)
Hi @DemoFour
1. Place Question category in a table visual
2. Place this measure in the visual
Measure =
COUNTROWS( DISTINCT( Table1[Client Number] ) )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB
I'm wrong, you are correct! I have a large data set and just taken it back to excel to check and the numbers are correct for each category. . .
I can't believe it, after all the attempt's and complex DAX it was so simple. I did not believe it as my original code gave the same results, but they seemed so high, i doubted them!
Hi @AlB
That does not give me a distinct count of the category, it gives me the count of all the questions answered by clients.
What I am after, is a count of the categories that the questions are in.
I have used this in my actual report and it it is not giving the correct results
Can you post the data in a table by columns? (It's easier to copy and paste)
Proud to be a Super User!
Paul on Linkedin.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
75 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |