Help using the CALCULATE with FILTER ALL

[heidibb]

Hello,

I am struggling with the performance of this calculation --  it is causing my visuals to spin and is using up a lot of resources. 

Success LY UG =

CALCULATE
(
[% Final Grade Success],

filter(all(sp_TermData),sp_TermData[TermAxis] = max(sp_TermData[TermAxis])-6),

filter(all(sp_TermData),sp_TermData[TermAcademicLevelCode] = "UG")
 )

I have tried this alternative, which seems like it should work fine, but it doesn't recognize the math of max(sp_TermData[TermAxis]-6. The result is just null. The TermAxis field is formatted as a number, so it SHOULD work, I just can't figure out why it's not.

Success LY UG =

VAR TermAxis = max(sp_TermData[TermAxis])-6

RETURN

CALCULATE([% Final Grade Success],

sp_TermData[TermAxis] = TermAxis,

sp_TermData[TermAcademicLevelCode] = "UG"

    )

Any thoughts on an alternative.

[heidibb]

Your suggestion is giving me all null values.

I have narrowed down the fact that it doesn't like the math on max(termaxis) - 6 directly in the calculation. Couple checks I've done:

- When I do a check calculation simply CALCULATE(max(sp_TermData[TermAxis]) - 6), and look at the result, I get what I want.
- When I do a check calculation of UG LY TermAxis = CALCULATE(max(sp_TermData[TermAxis]) - 6,sp_TermData[TermAcademicLevelCode] = "UG"), I get what I want.

- When I use that UG LY Termaxis in my calculation like this:

Success LY UG =

CALCULATE (

       [% Final Grade Success],

               sp_TermData[TermAxis] = [UG LY TermAxis]

       )

   I get an error that says "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression."

I have used the performance analyzer a bit, but not sure how to use it to figure out the issue on this calculation.

[heidibb]

That didn't quite work, but I did get something to render using the calculation in the screen shot... however, it's giving me the "TY" rate in my "LY" calculation. It's not seeing the math I'm doing on the max(termaxis) - 6 for some reason.
For example, I want the .774 rate to show in the Success LY UG field for the 22EW4 column since that comes from the 21EW4 term (1 year prior). We use what's called Term Axis to find various prior terms.

[sevenhills]

Curious what would you get if you do this:

Success LY UG = CALCULATE (
       [% Final Grade Success],

       filter(all(sp_TermData),
               sp_TermData[TermAxis] = max(sp_TermData[TermAxis])-6)

               && sp_TermData[TermAcademicLevelCode] = "UG"
       )
 )

if this work,s then the issue is you are trying to do filter with "all" twice and it has to do for each ... 

Optional: If the above does not work, You can try to see the DAX generated (sometimes helps), using Performance Analyzer and click the refresh button. 

             View Tab > Performance Analzer

             Home Tab > Refresh

🙂