Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 intended.
Extract 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.
Look forward to hearing from you
Pete
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
71 | |
49 | |
45 | |
20 | |
17 |