Forum Discussion
Rewrite Filter function with better option (Optimize Dax Measure)
Hi Guys! I need your help please, I have a model and I need to calculate a Sum of a column as a measure, the measure works fine, shows me the correct number, but when I begin to use other dimension...
I can solve it using lastnonblank function, everything works fine now
Try this:
var __maxMonthId = MAX( Work[month_id] )
var __result =
CALCULATE(
SUM( Work[pct_assignment_monthly] ),
KEEPFILTERS( 'Calendar'[month_id] = __maxMonthId ),
KEEPFILTERS(
TREATAS(
{1002, -1, 1001},
Project[project_category]
)
)
)
return
__result
Best
D
Thanks for the answer and sorry for my late response, I'd change the measure as you advise, but the time still increasing every time I add a new dimension, so I traced both measures, and there are the results:
This is the Trace using your proposed measure (use more FE than SE):
This is the trace using the first version of the measure (a little bit better un FE usage, but make 1 SE Query compared with the newest version of the measure)
So, another idea to replace the measure?
As an important finding, the filter that makes it slower is when evaluate Max(Month_id)
- Wait... What is the query that you measure? If you are pulling out of the model a lot of detail rows, then no wonder this is slow. I'd be surprised if you pulled out a fairly small amount rows from your dimensions and got such results as above. I smell something fishy in the query because my measure does not use fancy math, only filtering and therefore SE should prevail. In the measure there's not even a single IF that would call for a CALLBACK.
Please show us the query and tell us how many rows you pull out.
Best
D