Forum Discussion
Rewrite Filter function with better option (Optimize Dax Measure)
- Anonymous6 years ago
I can solve it using lastnonblank function, everything works fine now
As an important finding, the filter that makes it slower is when evaluate Max(Month_id)
Please show us the query and tell us how many rows you pull out.
Best
D
- Anonymous6 years agoNot applicable
These 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]
)- Anonymous6 years agoNot applicableOK. How many rows are you pulling out in total?
Best
D- Anonymous6 years agoNot applicable
4371 rows (including the total), if I put all dimensions of the model, the result will be bigger, the fact table has 730K rows.