cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Dax for sum of distinct values

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.

2 ACCEPTED SOLUTIONS
Super User

@Anonymous , Try a measure like

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

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Super User

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

6 REPLIES 6
Super User

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
Not applicable

HI @AlB

Thanks

It works but the total is not correct

Super User

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
Not applicable

Thanks @AlB for your detailed explanation

Super User

@Anonymous , Try a measure like

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

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Regular Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors