Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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.
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!
Solved! Go to Solution.
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.
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
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.
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
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! 🙂
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
User | Count |
---|---|
128 | |
73 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |