Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
bobbarker
New Member

DAX count in date range (slicer)

Hi togheter,

I got a challange where I can need some support / hints of the community 🙂

The data source is a list where a row is generated every time a student take an exam. Looks like this:

a.PNG

 

On top of that data source, I need an report where I see the levels and the number of students who are in that level depending on the time slicer. If a student is in two levels, it should only take the higher level.

b.PNG

 

I tried the following calculation:

 
 Number of students per level = 
CALCULATE (

DISTINCTCOUNT ( List[STUDENT] );
FILTER (
'List';
'List'[LEVEL]
= CALCULATE (
MAX ( List[LEVEL] );
ALLEXCEPT( List; List[DATE])
)
)
)

As you can see in the second screenshot, John is counted two times.

Anyone a solution and hint?

Thanks in advance!

1 ACCEPTED SOLUTION
mwegener
Most Valuable Professional
Most Valuable Professional

Hi @bobbarker ,

 

check this out

Number of students per level =
VAR MaxLevelPerStudent =
    SUMMARIZE (
        ALLEXCEPT ( List, List[DATE] ),
        List[STUDENT],
        "@LEVEL", MAX ( List[LEVEL] )
    )
RETURN
    COUNTROWS (
        FILTER ( MaxLevelPerStudent, [@LEVEL] = SELECTEDVALUE ( List[LEVEL] ) )
    )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

3 REPLIES 3
mwegener
Most Valuable Professional
Most Valuable Professional

Hi @bobbarker ,

 

check this out

Number of students per level =
VAR MaxLevelPerStudent =
    SUMMARIZE (
        ALLEXCEPT ( List, List[DATE] ),
        List[STUDENT],
        "@LEVEL", MAX ( List[LEVEL] )
    )
RETURN
    COUNTROWS (
        FILTER ( MaxLevelPerStudent, [@LEVEL] = SELECTEDVALUE ( List[LEVEL] ) )
    )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @bobbarker ,

This is a cool question but please never post pictures of your data but a copy or a link to a PBIX/xlsx file so we can actually help you create the required DAX. This is potentially a complicated measure and having some testdata helps in developing it. 

 

Is it possible to share that?
Thanks!

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @JarroVGIT,

thanks for your respone. Under the link, you can find the pbix file with the defined set.

Best

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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