Distinct Count of Category based on client ID

[DemoFour]

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. 

[PaulDBrown]

@DemoFour

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] )
    )
)

[DemoFour]

@PaulDBrown 

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]
    )
)

[AlB · Accepted 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 

[DemoFour]

@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! 

[DemoFour]

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

[PaulDBrown]

@DemoFour 

Can you post the data in a table by columns? (It's easier to copy and paste)

[DemoFour]

@PaulDBrown 

Sorry, I have corrected now!