Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

DAX
optimize
  • Anonymous's avatar
    Anonymous
    6 years ago

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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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