Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
3 years ago
Solved

Slow performance in Matrix / Measure optimization

Hello community,

 

I created a matrix and the results are fine. But when I switch slicer from 2020 to 2021 it takes 14000 ms. When I enable the subtotals 6600 ms. Is that normal or would some optimization of the measures help?

 

I have a table with start and end dates. calculate a new end date:

 

Measure1 =
IF (
    MAX(TABLE[END_DATE]) = BLANK(),
    DATE(SELECTEDVALUE(CALENDER[YEAR]), 12, 31 ),
    IF (
        MAX(TABLE[END_DATE]) <= DATE(SELECTEDVALUE(CALENDER[YEAR]), 12, 31),
        MAX(TABLE[END_DATE]),
        DATE(SELECTEDVALUE(CALENDER[YEAR]), 12, 31)
       )
    )
 
Then a  lengh of time
 
Measure2 =
DATEDIFF(MAX(TABLE[START_DATE]), [Measure1], DAY) / 365
 
Then a aggregation
 
Measure3=
AVERAGEX(FILTER(TABLE, [Measure2] > 0) , [Measure2])
 
In the Matrix I show two columns. A Group and the Measure3 values. 
 
Someone who see a possibility to make the measures faster? Would be interessting to know. 
 
Thanks.
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    Using variables might improve performance

    Measure1 =
VAR MaxEndDate =
    MAX ( TABLE[END_DATE] )
VAR YearEnd =
    DATE ( SELECTEDVALUE ( CALENDER[YEAR] ), 12, 31 )
RETURN
    IF (
        ISBLANK ( MaxEndDate ),
        YearEnd,
        IF ( MaxEndDate <= YearEnd, MaxEndDate, YearEnd )
    )

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Using variables might improve performance

    Measure1 =
VAR MaxEndDate =
    MAX ( TABLE[END_DATE] )
VAR YearEnd =
    DATE ( SELECTEDVALUE ( CALENDER[YEAR] ), 12, 31 )
RETURN
    IF (
        ISBLANK ( MaxEndDate ),
        YearEnd,
        IF ( MaxEndDate <= YearEnd, MaxEndDate, YearEnd )
    )