Latest Data visualization based on filter selection

[shoeb1359]

Hello Experts
I have a scenario where I need to display total no of students enrolled in different schools, with a month filter (multi-select).
Challenge here is, some students got transferred from one school to another during the year. When we visualize no of students by school, the transferred students are counted more than once. End user is requesting for "no of students by school" visual to consider students latest enrollment when no filter is selected, but if month filter is selected, he/she wants to see the exact count for that month, even if the student is not enrolled currently in that school

Month Year	School Name	Student id	IsCurrent
Jan-23	A	101	No
Feb-23	B	102	No
Mar-23	C	101	Yes
Apr-23	D	102	Yes

v-zhangti

[shoeb1359]

Thanks it's working . Keep up the good work

[qqqqqwwwweeerrr · Accepted solution]

Hi @shoeb1359 

You can achieve this by creating three measure

1. 

Latest Enrollment =

CALCULATE(

    COUNTROWS(

        DISTINCT('Enrollment'[Student id])

    ),

    FILTER(

        'Enrollment',

        'Enrollment'[IsCurrent] = "Yes"

    )

)
2. 

Students Per Month =

CALCULATE(

    COUNTROWS(

        DISTINCT('Enrollment'[Student id])

    ),

    REMOVEFILTERS('Enrollment'[IsCurrent])

)
3. 

Final Student Count =

IF(

    ISFILTERED('Enrollment'[Month Year]),

    [Students Per Month],

    [Latest Enrollment]

)use thired meaure in axis of a chart here is the output 
when nothing is selected the latest one be is shown

when selected in filter

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards