Forum Discussion
Help using the CALCULATE with FILTER ALL
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.
(
[% Final Grade Success],
)
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.
Any thoughts on an alternative.
3 Replies
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 AnalzerHome Tab > Refresh🙂
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.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.
