DAX cmd/filters to only include results that exist in both academic years & quarters but diff quarte

[TravellingMagpi]

Hi guys, 1st post so apologies if i miss anything obvious. I linked a sample PBIX file here which contains a single table named dt_StudentResults. 

Brief Overview:  

Our school results are measured against a target Proficiency Rate of 80% for each year, grade, subject - which we do via a measure, and filtered using the slicers present on the page. 

Now the school wishes to capture & compare the same data across two academic years, using the same quarter, but a different grade, specifically only including the student results where they exist in both years  

In the tableThe Candidate Number field is the unique student reference posted against every student result. 

Some further detail 

This is our current measure to calculate a the PR for the scope set via slicers of a single year, quarter and grade. 

M.PR = (CALCULATE(COUNT('dt_StudentResults'[Value]), 'dt_StudentResults'[Value]>=.8)/COUNT('dt_StudentResults'[Value]))

Explanation: 

At the Academic Year 2021-2022, Quarter 4, Grade 5 there are 31 students studying both subjects (English and Science)

- 13 students left Grade 5

In Academic Year 2022-2023, Quarter 4, Grade 6 = there are now 18 students studying both subjects (English and Science)

• In scope of the analysis should be 18 students who were promoted from Grade 5.

Any new students joining the school into Grade 6 will be ignored as they were not also in Grade 5.

Challenge:

What is the best way to complete the analysis of the 18 students who remain, displaying the results from 2021-2022 Q4 Grade 5 and then comparing them to 2022-2023 Q4 Grade 6

Thanks in advance,

[TravellingMagpi]

Hi Xiaoin,  thought i had included when i posted, apologies

See attached link here to the demo pbix file Click Me 

[v-shex-msft]

HI @TravellingMagpi,

You can try to use the following measure formula, I add a variable to get the students list which both existed in all selected years:

M.PR =
VAR yearList =
    VALUES ( dt_StudentResults[Academic_Year] )
VAR studentList =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                FILTER ( ALLSELECTED ( dt_StudentResults ), [Academic_Year] IN yearList ),
                [Candidate_Number],
                "RowCount", COUNT ( dt_StudentResults[Academic_Year] )
            ),
            [RowCount] = COUNTROWS ( yearList )
        ),
        "Candidate_Number", [Candidate_Number]
    )
RETURN
    CALCULATE (
        DIVIDE (
            CALCULATE (
                COUNT ( 'dt_StudentResults'[Value] ),
                'dt_StudentResults'[Value] >= .8
            ),
            COUNT ( 'dt_StudentResults'[Value] ),
            -1
        ),
        FILTER (
            ALLSELECTED ( 'dt_StudentResults' ),
            [Candidate_Number] IN studentList
        )
    )

Regards,
Xiaoxin Sheng

[TravellingMagpi]

Hi Xiaoxin and thank you for your reply.

Is it possible to show the calculated % for each subject in each year (for those students who were present in each year & quarter who scored >=.8)  Using the example pbix file, when i select both English & Science to be in scope, we lose the value from the measure.

In summary we know, there are 18 students in 2022-2023 Q4 and there are 31 in 2021-2022 Q4

• Of those 18 students present in 2022-2023 Q4, all were present in 2021-2022 Q4
• 2022-2023 is Grade 6 and 2021-2022 is Grade 5

The results from that student set in 2022-2023 Q4 there are:

• 13 who scored >=0.8 in English (72%)
• 8 who score >=0.8 in Science (44%)

The results from that same student set but for 2021-2022 Q4 there are:

• 9 who scored >=08. in English (50%)
• 11 who scored >=0.8 in Science (61%)

So we need to present the calculated % for each subject in each year.

Thanks again for your help

[TravellingMagpi]

Hi Xiaioxin,

Looking again at your proposal, i think it just needs to recognise the Grade change.

In our example, 2022-2023 students are Grade 6, but in 2021-2022 students are in Grade 5.

thanks

[v-shex-msft]

HI @TravellingMagpi,

Perhaps you can try to use the following formula, I modify the variable to extract the list based on year and grade groups:

M.PR =
VAR yearGradeList =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                ALLSELECTED ( 'dt_StudentResults' ),
                "YearGrade",
                    [Academic_Year] & "-" & [Grade]
            ),
            "YearGrade", [YearGrade]
        )
    )
VAR studentList =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                FILTER (
                    ALLSELECTED ( 'dt_StudentResults' ),
                    [Academic_Year] & "-" & [Grade] IN yearGradeList
                ),
                [Candidate_Number],
                "RowCount", COUNT ( 'dt_StudentResults'[Candidate_Number] )
            ),
            [RowCount] = COUNTROWS ( yearGradeList )
        ),
        "Candidate_Number", [Candidate_Number]
    )
RETURN
    CALCULATE (
        DIVIDE (
            CALCULATE (
                COUNT ( 'dt_StudentResults'[Value] ),
                'dt_StudentResults'[Value] >= .8
            ),
            COUNT ( 'dt_StudentResults'[Value] ),
            -1
        ),
        FILTER (
            ALLSELECTED ( 'dt_StudentResults' ),
            [Candidate_Number] IN studentList
        )
    )

For above formula, you may need to change your slicer to hierarchy mode to use year and grade fields at the same time. (For your description, it seems like your selections are year grade combo instead operation on two standalone slicers)

Regards,

Xiaoxin Sheng

[TravellingMagpi]

Thanks again @v-shex-msft 

As suggested, ive made the slicer to hierarch mode with both year and grade.

When i select an single year / grade combo the calculated value is shown corretly, (using only the Engllish subject for explanation).  Here is 2022-2023 Grade 6 single year selected where 13 of the 18 students >=80%

Below is 2021-2022 Grade 5 single year selected, where 17 of 31 students >=80%.

When i then choose the two together, i belive the calculation is showing the average of the two M.PR results - 72%, 55%, resulting in 61%

However, we know that only 18 students existed in both years, so in the English subject:

• 9 of the 18 scored >=80% in year 2021-2022, therefore 50% is the value for 21-22
• 13 of the 18 scored > 80% in year 2022-2023, therefore 72% is the value for 22-23

We would like to display the values from both years as opposed to the combined value

Is this possible to achieve in the measure you kindly provided?

Regards,

[v-shex-msft]

Hi @TravellingMagpi ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng