Performance Dax Query Help

[fcarvalho]

Hi guys,

I'm trying to improve my dax queries, but it still takes about 7000ms to load a table.
I have these 4 measures, will it be possible to improve performance using other functions? If so can you help me?

Slowness is only in the Final measure (Val Li Final LY).

Val Li: 240ms

Val Li LY: 180ms

Val Li Has LY: 500ms

Val Li Final LY: 7000ms

Val Li =

CALCULATE( SUM( table[Vlr] ) + SUM( table[Vlr_R] ) )

Val Li LY =

VAR SelectedDate = MIN('Calendario Carteira'[Date]) // 4

VAR SelectedYear = SELECTEDVALUE('Calendario Carteira'[Ano]) // 2021

VAR SelectedMin = CALCULATE( MINX(VALUES('table2'[StartInicio]),'table2'[StartInicio]), ALL( Calendario ) )

VAR SelectedMax = CALCULATE( MAXX(VALUES('table2'[EndFim]),'table2'[EndFim]), ALL( Calendario ) )

RETURN

IF.EAGER(SelectedDate >= SelectedMin && SelectedDate <= SelectedMax,

CALCULATE( [Val Li],

FILTER(

ALL('Calendario'[Date]),

YEAR(Calendario[Date]) = SelectedYear-1 ) ), BLANK() )

Val Li Has LY =

CALCULATE( [Val Li LY],

FILTER(RLSF,

RLSF[Level 9] = [RLS]

|| RLSF[Level 8] = [RLS]

|| RLSF[Level 7] = [RLS]

|| RLSF[Level 6] = [RLS]

|| RLSF[Level 5] = [RLS]

|| RLSF[Level 4] = [RLS]

|| RLSF[Level 3] = [RLS]

|| RLSF[Level 2] = [RLS]

|| RLSF[Level 1] = [RLS]

)

,TREATAS( VALUES( Calendario[Date] ), table[Data] )

,TREATAS( VALUES( table2[key] ), table[key] )

,CROSSFILTER( table[key2], table2[key2], None )

)

FINAL MEASURE

Val Li Final LY =

CALCULATE( SUMX( VALUES(table2[key]), CALCULATE( [Val Li Has LY] ) ) )

[fcarvalho]

Hi @v-kelly-msft ,

I had come to the conclusion that with Values () or Summarize () it would be better to use Addcolumns (), so I added Addcolumns to my sumx, values and put a Filter in the last measure. Now I have 400ms , it improved the performance!
I will follow your advice too, thank you very much

[v-kelly-msft · Accepted solution]

Hi  @fcarvalho ,

You need to improve Val Li LY first:

VAR SelectedMin = CALCULATE( MINX(VALUES('table2'[StartInicio]),'table2'[StartInicio]), ALL( Calendario ) )
VAR SelectedMax = CALCULATE( MAXX(VALUES('table2'[EndFim]),'table2'[EndFim]), ALL( Calendario ) )

You can simply use below dax expression:

VAR SelectedMin = MINX(VALUES('table2'[StartInicio]),'table2'[StartInicio]))
VAR SelectedMax = MAXX(VALUES('table2'[EndFim]),'table2'[EndFim]))

For measure Val Li Has LY，you could first create 2 columns as below:

Column = 'Table'[Level 1]&'Table'[Level 2]&'Table'[Level 3]&'Table'[Level 4]&'Table'[Level 5]&'Table'[Level 6]&'Table'[Level 7]&'Table'[Level 8]&'Table'[Level 9]

search_Rls = IF(SEARCH("RLS",'Table'[Column],1,0)>0,1,BLANK())

And you will see:

Then modify your measure as below:

Val Li Has LY =
CALCULATE( [Val Li LY],
FILTER(RLSF,
RLSF[search_Rls]=1),
,TREATAS( VALUES( Calendario[Date] ), table[Data] )
,TREATAS( VALUES( table2[key] ), table[key] )
,CROSSFILTER( table[key2], table2[key2], None )
)

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!