Forum Discussion

shoeb1359's avatar
shoeb1359
Helper III
1 year ago
Solved

Latest Data visualization based on filter selection

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 YearSchool NameStudent idIsCurrent
Jan-23A101No
Feb-23B102No
Mar-23C101Yes
Apr-23D102Yes



 

v-zhangti

Need Help
  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    1 year ago

    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

2 Replies

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    Solution Sage
    1 year ago

    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