How To Calculate Number Of Completed Courses Per Student - Dynamic Grouping In Power BI using DAX

Power BI
17

Ok please see below and I’ll place a link to the model

This is the output

image
image871×401 6.61 KB

These are all the formula you need

Unique Students = COUNTROWS( VALUES( 'Registration Data'[Student ID] ) )

Unique Registrations = COUNTROWS( 'Registration Data' )

image

Now to get the table you need to use some advanced logic. This is exactly the same as the dynamic grouping technique explained here

Need to create a table like this (doesn’t have to be exact, but close enough)

image

Then use a formula like this

Courses Completed = 
CALCULATE( [Unique Registrations],
    FILTER( VALUES( 'Registration Data'[Student ID] ),
        COUNTROWS(
             FILTER( 'Completed Courses Grouping',
                 [Unique Registrations] > 'Completed Courses Grouping'[Min] &&
                 [Unique Registrations] <= 'Completed Courses Grouping'[Max] ) ) > 0 ) )

This will give you this

image

Which I believe is what you need based on the logic you have explained.

Here’s a link to the demo file I created

https://enterprisedna.co/wp-content/uploads/2018/03/Academic-data.zip

1 Like