Hi guys
I am coming (hopefully!) to the end of writing a pbix to handle management accounting for a large multidept business and am trying to make use of variables to speed up the DAX.
However if I create a variable referring to a measure which has a filter statement in it and then in the return statement try to further apply the filter it does not action the variable as wanted.
Excerpt of problem below:
Measure MAarg1 = CALCULATE([MASum], filter(all(MAReports),MAReports[AccountKey]=max(MAReports[MAKPI_Arg1])))
Then in another measure reference the above via a variable
Var vMAArg1 = [MAArg1]
Return
Switch([MAKPI],
blah, blahresult,
blah2, blah2result,
“Filter11”, calculate(vMAArg1,FILTER(All(OperationCodes),OperationCodes[DeptCode]=11)),
Etcetera)
The above does not perform the dept code filter, but returns an unfiltered result.
However if I replace vMAArg1 with a reference to the original measure [MAArg1] all is good!
it would be good to get this to work since the switch statement is quite large and the use of variables in other areas of the problem measure have given significant performance gains.
I think the problem lies in the fact that there are 2 filter contexts going on, but not really sure.
I believe you will want to watch a video from Sam on SWITCH. At a brief glance without having a model to look at, I can see an issue with the way you have your SWITCH statement setup. Review the video and have a go at it. If you get stuck, let us know and we would be glad to help out.
The SWITCH statement is used in two different ways:
XYZ =
VAR vMAArg1 = [MAArg1]
RETURN
SWITCH ( [MAKPI], "value1", "Result1", "value2", "Result2", "Alternate value" )
The expression (first parameter) in the SWITCH function returns a single scalar value where the expression is to be evaluated for each row/context.
* value1 and value2 are constant values to be matched with the results of the expression
* result1 and result2 are any scalar expressions to be evaluated if the results of expression match the corresponding value
* else is any scalar expression to be evaluated if the result of expression doesn't match any of the value arguments.
XYZ =
VAR vMAArg1 = [MAArg1]
RETURN
SWITCH (
TRUE (),
vMAArg1 <= xyz, result1,
vMAArg1 <= abc, result2,
"Alternate value if none are true"
)
TRUE() is a DAX function
The first boolean expression that evaluates to True will return the corresponding result.
The rule of thumb is that when a value is assigned to a variable (VAR), the VAR becomes a constant value in the RETURN part. Not even a CALCULATE can override it.
Simply put, your VAR vMAArg1 = [MAArg1] statement is evaluated in the current filter context and when it is passed to the RETURN statement it becomes a constant value and cannot be changed even by a CALCULATE.
I’m not completely sure of what I’m saying, so I would really appreciate if another member could confirm or nuance my statement.
I would generally agree with that statement. The one additional complexity is if a VAR is defined within an iterator, the value of the variable is assigned for every row iterated (i.e, VAR can be a set of constants, but the general concept is still the same).
@sam.mckay does a nice job explaining the evaluation of variables in this video:
Thanks Brian and DiBest for help here. Just watched Sam’s videos on variables and he captures my problem exactly. Unfortunately that takes me back to a measure that is slow to respond 12secs so not sure what to do next.
It would appear that MAArg and vMAArg are not the issue, but that the context reset piece in the switch statement is not working for all the reasons Sam explains. I have quite a few lines in the switch statement that rely on resetting the context via a filter statement so any tips on how to do that??
Pete
Hi Guys
Spent day a bit disappointed!! My measure still takes ages to process.
I’ve viewed lots of Sam’s videos on dax optimisation etc etc but am completely stuck.
Now that I have a cascaded model, with no bidirectional filters I am totally puzzled.
Basically my measure is for a set of financial statements. It inserts various kpis (Gross %, per unit amounts etc) as rows in a report dimension table. So lots of context re-filtering and division via a switch statement.
Would calculate table help and is it faster than Filter All? - doubt it?
Any ideas how to speed up? Could I share full measure syntax or even pbix?
Pete
Yes, please do share your PBIX file - that is always to best way to seek a solution that will meet your requirements, particularly for complex optimization issues like this one.
Just checking you’ve gone through the financial reporting course, as this goes into detail around how to use SWITCH to allocate values into certain rows.
Here’s some examples
See how you go with these…I’m confident if you follow through the techniques described in this course you should be able to work it out.
You’ll notice that I don’t actually use variables that often in the example because you just don’t need them.
A pbix model of the scenario will also likely help in getting this solved quicker
Hi,
Spent a bit of time looking at your financial templates. I am broadly working on the same principal but my profit statement template has a relationship with the fact table - with the SQL server feed for the fact table feeding totals as well. (Essentially a Trial balance that doesn’t balance because the totals are fed through as well).
I’m gonna give your detached income statement template a go and see where that takes me.
Do you think it may solve the issue of speed?
Pete
Hi Sam
Your videos here have been a great help but I am still experiencing Speed Issues, but I’ll close this call down as solved because the subject has been managed and open a separate on if that’s OK?
Pete
Try not to use table filters as they can have performance ramifications and can lead to complex behaviour
Speed issues are more than likely due to your data model not being set up optimally. Remember DAX is tuned to work with star schemas, though snowflake is ok
Try to do as much in the ETL stage (power query, or in sql if available)
