Rewrite Filter function with better option (Optimize Dax Measure)

[JulianEscobar]

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 dimensions, the time expended for the visual that use this metric begin to increase (the visual has only that measure), so I was analyzing the measure, and I find the filter that makes the visual slower (is a filter in the metric), it's highlighted in another image, so I want to replace that filter, but I don't know-how, can you give me any advice?   Below you can find the measure, the model and the current results of a visualization (that are correct). 

[JulianEscobar · Accepted solution]

I can solve it using lastnonblank function, everything works fine now

[Anonymous]

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

[JulianEscobar]

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?

[JulianEscobar]

As an important finding, the filter that makes it slower is when evaluate Max(Month_id)

[Anonymous]

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

[JulianEscobar]

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]
)

[Anonymous]

OK. How many rows are you pulling out in total?

Best
D

[JulianEscobar]

4371 rows (including the total), if I put all dimensions of the model, the result will be bigger, the fact table has 730K rows.

[Anonymous]

Yeah... This is a lot of rows. For a reporting tool that should summarize data, this is really unusual to return that many rows. Also, the speed depends on the hardware you use.

I suppose that for a decent amount of rows the measure will be very fast.

Best
D

[JulianEscobar]

But everything works fine if I remove the Date filter, this one:   FILTER ( 'Date', maxMonthid = 'Date'[Month ID] )

Obviously, the result of the measure doesn't work for me.  So, there is another idea to replace it?