Calculate with all option

[mjmcs13]

Good day,
I have issue using the ALL filter.

this is my measure syntax also please see attachment

SB Math Average =
VAR ALL_SB_MAth =
FILTER(Assessments,Assessments[TEST_SHORTNAME] = “SB” && Assessments[SUBLEVEL1_SHORTNAME] = “MATH” )
VAR ALLDIST=
ALL(DISTRICT[DISTRICT_ID])

//filter by all school
VAR ALLSchool =
ALL(School_LKP)

  RETURN
    CALCULATE(
        AVERAGEX(Assessments, Assessments[Performance_Score]),
       ALLSchool,
       ALLDIST,
         ALL_SB_MAth 
)

Calculate with all option
Filter By all schools.pbix (130.1 KB)

[Harsh]

Hello @mjmcs13,

Thank You for posting your query onto the Forum.

Well, in this case, I’m presuming that when you say “ALL” option is not working you mean that eventhough when you make a selection in the slicer for Schools, the percentages should not change upon those selections.

Below is the measure alongwith the screenshot of the final results provided for the reference -

SB Math Average - Harsh = 
CALCULATE(
    CALCULATE(
        AVERAGEX( Assessments , 
            Assessments[Performance_Score] ) ,
        FILTER( Assessments , 
            Assessments[TEST_SHORTNAME] = "SB"  && 
            Assessments[SUBLEVEL1_SHORTNAME] = "MATH"  ) ) , 
    ALL( School_LKP ) )

I’m also attaching the working of the PBIX file as well as providing few of the links below of the posts which our expert @AntrikshSharma has created.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Filter By all schools - Harsh.pbix (130.9 KB)

CALCULATE Mantra

Here are some steps that I recite when I am stuck with a complex problem, it is always good remember CALCULATE’s execution order. Evaluates all the filter arguments of CALCULATE in the original filter context that is active outside CALCULATE and then are kept aside CALCULATE creates a new Filter by copying the original filter context If it is called in a Row Context, either by an iterator or Calculated column then it takes that particular row and create an equivalent filter from values …

How filters are evaluated in CALCULATE

Someone asked this question on Power BI Community forum and I think it will be a good learning for those who are new to DAX as when I was starting to learn it was unclear for a very long time that how 2 similar expression are calculating differently.

[mjmcs13]

Hi Harsh,

This sample will give me a good explanation of using the ALL function.
I do believe I should have 3 result for each year. Sample uses year 2018
Example When Year is selected – Then I have from the 3 Option from the district slice

ALL District should give me Schools 410 418 425 441 and 449
DISTRICTID =‘001’ schools → 410 418 425
.DISTRICTID =‘002’ Schools ->441 and 449

Below is a sample SQL script

– All Schools
SELECT avg (Performance_Score)
FROM Assessments a
Join School_LKP s on A.DISTRICTID = s.DISTRICT_ID
WHERE S.school_Year= 2018
AND Assessments[TEST_SHORTNAME] = “SB” and
Assessments[SUBLEVEL1_SHORTNAME] = “math”

		-- 410 418 425 School

SELECT avg (Performance_Score)
FROM Assessments a
Join School_LKP s on A.DISTRICTID = s.DISTRICT_ID and A.DISTRICTID =‘001’
WHERE S.school_Year= 2018
AND Assessments[TEST_SHORTNAME] = “SB” and
Assessments[SUBLEVEL1_SHORTNAME] = “math”

–441 and 449
SELECT avg (Performance_Score)
FROM Assessments a
Join School_LKP s on A.DISTRICTID = s.DISTRICT_ID and A.DISTRICTID =‘002’
WHERE S.school_Year= 2018
AND Assessments[TEST_SHORTNAME] = “SB” and
Assessments[SUBLEVEL1_SHORTNAME] = “math”

[mjmcs13]

this also a followup

Below is the results I am expected. because I am filtering by year and District then all schools within that selection. The result you send me is for All of 2018 which is correct but the result should change when the year changes also.

The information below is base on 2018 results. I do believe I should have 3 different number within school year

Please see attach excel sheet with data break down an summary

ALL	All Schools		37.9%
001 District	410 - 418 - 425		38.3%
002 District	441 - 449		37.4%

[Harsh]

Hello @mjmcs13,

Now, this really helps when you provide what end results you’re actually trying to achieve. So just a minor tweak in the measure. Under the “ALL()” function, previously it was referenced as entire table i.e., “ALL( School_LKP ) )”, just change it to “ALL( School_LKP[SCHOOL_NAME] ) )” and now you’ll be able to see the results as you’re expecting. Below is the overall measure alongwith the screenshot of the final results provided for the reference purposes -

SB Math Average - Harsh = 
CALCULATE(
    CALCULATE(
        AVERAGEX( Assessments , 
            Assessments[Performance_Score] ) ,
        FILTER( Assessments , 
            Assessments[TEST_SHORTNAME] = "SB"  && 
            Assessments[SUBLEVEL1_SHORTNAME] = "MATH"  ) ) , 
    ALL( School_LKP[SCHOOL_NAME] ) )

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Filter By all schools - Harsh v2.pbix (131.0 KB)

[mjmcs13]

Thank you very much. I have also got a better understanding of using the ALL function because it was my data and I know what the result should be using SQL script which is my strength.

Thank you for you expertise and patient.

[Harsh]

Hello @mjmcs13,

You’re Welcome.

I’m glad that I was able to assist you and you found it helpful.

Thanks and Warm Regards,
Harsh

[mjmcs13]

Can you please explain the embedded Calculate?

This is the first time I have seen an embedded Calculate statement

I am try to figure out the order of when the statements are execute

When does this statement execute ALL(lkp_School[SCHOOL_NAME] ) because I assume this is in the outer Calculate