Bottom N based on Condition

[AyeshaA10]

Hi!

I am very new to Power BI and this is my first post here on the Power BI forums. I have previously found quite a few gems on here that I applied to my work. I am hoping I might get an answer to my problem below:

I am working with a student academic performance dataset. I am required to create a list of top 10 best performing students as well as a list of 10 at-risk (worst performing) students. This is easily done using Filters. Where I am facing an issue is I need to add a condition to the list of 10 at-risk students. If the total mark is below 50, then I show them on the list, otherwise I leave the list empty. My pseudocode is something like this,

If 'GradingData'[Total] < 50, then show in table else leave table empty.

How can I go about achieving this? Thank you in advance!

[archuleta28 · Accepted solution]

Hi @AyeshaA10 ,

You can use filters in your case to showcase your results:

Hope this helps, please let me know if this doesnot work out.

[johnt75 · Accepted solution]

Create a measure like

Is At Risk =
VAR CurrentStudent =
    SELECTEDVALUE ( 'Grading Data'[Student ID] )
VAR AtRiskStudents =
    TOPN ( 10, ALL ( 'Grading Data' ), 'Grading Data'[Total], ASC )
VAR VisibleAtRiskStudents =
    SELECTCOLUMNS (
        FILTER ( AtRiskStudents, 'Grading Data'[Total] < 50 ),
        'Grading Data'[Student ID]
    )
VAR Result =
    IF ( CurrentStudent IN VisibleAtRiskStudents, 1 )
RETURN
    Result

and add this as a visual level filter to only show when the value is 1

[johnt75 · Accepted solution]

Create a measure like

Is At Risk =
VAR CurrentStudent =
    SELECTEDVALUE ( 'Grading Data'[Student ID] )
VAR AtRiskStudents =
    TOPN ( 10, ALL ( 'Grading Data' ), 'Grading Data'[Total], ASC )
VAR VisibleAtRiskStudents =
    SELECTCOLUMNS (
        FILTER ( AtRiskStudents, 'Grading Data'[Total] < 50 ),
        'Grading Data'[Student ID]
    )
VAR Result =
    IF ( CurrentStudent IN VisibleAtRiskStudents, 1 )
RETURN
    Result

and add this as a visual level filter to only show when the value is 1

[AyeshaA10 · Accepted solution]

Thank you very much for your help! I've saved this DAX measure. I think this is going to come in handy later.

[archuleta28 · Accepted solution]

Hi @AyeshaA10 ,

You can use filters in your case to showcase your results:

Hope this helps, please let me know if this doesnot work out.

[AyeshaA10]

Thank you so much for your help. This worked great! 

[archuleta28]

Glad to help! 🙂