Dax for sum of distinct values

[Anonymous]

Hi

I have a dataset which has a student ID and Course-name. One student takes multiple courses. Below is my dataset sample

I need output as below

How to do the DAX calculation for the above? I checked an existing question on the same but not successful.

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

sumx(values(Table[student_id]) , calculate(DISTINCTCOUNT(Table[Course])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[AlB]

Hi @Anonymous 

1. Place Student ID in the rows of a table visual

2. Create this measure and place it in the visual

Measure = 

DISTINCTCOUNT(Table1[Course])

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 

[Anonymous]

HI @AlB 

Thanks

It works but the total is not correct

[AlB · Accepted solution]

@sdhilip

Yes, if you want the total as the sum of the individual rows, you must change the code as already suggested.

In the total row that you want to replicate the behavior to the visual, and to do so you must have all dates in rows, apply the measure to each row, and then sum the result of each row. That's what you do with the new code:

SUMX (
    DISTINCT ( Table1[Student ID] ),
    CALCULATE ( DISTINCTCOUNT ( Table1[Course] ) )
)

Note that CALCULATE needs to trigger the context transition and therefore count courses only for current students.

In any of the other rows in the visual, you have a specific StudentID as the filter context, so DISTINCT() will actually return a single row with that particular StudentID. Then our initial measure is invoked in that row and the SUMX adds only that result, since we only have one row. Therefore, you will get the same result as applying the original measure directly.

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[Anonymous]

Thanks @AlB for your detailed explanation

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

sumx(values(Table[student_id]) , calculate(DISTINCTCOUNT(Table[Course])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Mike5049]

Can you generate a similar formula for a calculated column instead of a measure? When I applied this formula to a calculated column duplicate values weren't removed as in the measure?