Forum Discussion
Rewrite Filter function with better option (Optimize Dax Measure)
I can solve it using lastnonblank function, everything works fine now
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
DThese are the queries that I measured:
With your measure:
DEFINE
---- MODEL MEASURES BEGIN ----
MEASURE Work[Load] =
var maxMonthId = MAX( Work[month_id] )
var result =
CALCULATE(
SUM( Work[pct_assigment_monthly] ),
KEEPFILTERS( 'Calendar'[month_id] = maxMonthId ),
KEEPFILTERS(
TREATAS(
{1002, -1, 1001},
Project[project_category]
)
)
)
return
result
---- MODEL MEASURES END ----VAR __DS0FilterTable =
TREATAS({2020}, 'Calendar'[Year])EVALUATE
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Calendar'[month_description],
'Calendar'[month_id],
'Regions'[region_desc],
'Machine Sector'[sector_desc],
'Customer'[customer_desc]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
"Load", 'Work'[Load]
)------------------------------------------------------------------------------------------------
With the first version of the measure (I only changed maxMonthId in order to use as a variable):
DEFINE
---- MODEL MEASURES BEGIN ----
MEASURE Work[Load] =
VAR maxMonthid =
MAX ( 'Work'[month_id] )
VAR result =
CALCULATE (
SUM ( Work[pct_assigment_monthly] ),
FILTER ( 'Calendar', maxMonthid = 'Calendar'[month_id] ),
FILTER (
Project,
Project[project_category] = 1001
|| Project[project_category] = 1002
|| Project[project_category] = -1
),
CROSSFILTER ( 'Leave Reason'[leave_reason_id], Work[leave_reason], NONE )
)
RETURN
result---- MODEL MEASURES END ----
VAR __DS0FilterTable =
TREATAS ( { 2020 }, 'Calendar'[Year] )
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
ROLLUPGROUP (
'Calendar'[month_description],
'Calendar'[month_id],
'Regions'[region_desc],
'Machine Sector'[sector_desc],
'Customer'[customer_desc]
),
"IsGrandTotalRowTotal"
),
__DS0FilterTable,
"Load", 'Work'[Load]
)- OK. How many rows are you pulling out in total?
Best
D