Forum Discussion

ViralPatel212's avatar
ViralPatel212
Resolver I
2 years ago
Solved

Visual loading Slow

Hello    I have a table where the visual is running pretty slow.  Looking into Performance Analyser, i noticed the DAX query MS duration is 14506 and other is 513. Wondering if you could help to b...
Need Help
Show and Tell
Tutorial Requests
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    ViralPatel212 

     

    to know which measures are affecting the performance, 

    you can create a table per each measure and check from the performance analyser , each visual how much its dax code it taking. 

    this way we can filter out all measures that are dont affect performance. .

     

    can you please do it, to know which measures needs improvements ( if the improvement is possible ) 

     

     

ViralPatel212's avatar
ViralPatel212
Resolver I
2 years ago

Daniel29195  Apologies, here is the result acheived with the measures used:

 

Ranking = SUM('Dealer Ranking Tbl'[Size])
Vol =

   CALCULATE(
        [Ranking],
        FILTER(
        'Dealer Ranking Tbl',
        'Dealer Ranking Tbl'[Type] = "Vol. (MM)"))
Trades =

    CALCULATE(
        [Ranking],
        FILTER(
        'Dealer Ranking Tbl',
        'Dealer Ranking Tbl'[Type] = "Trades"))
 
LastM Vol =
var maxdate = EOMONTH(TODAY(),-1)
 var mindate = EOMONTH(TODAY(),-2) +1
VAR selectedfixedates = SELECTEDVALUE('DIM Date Filter (Dealer Ranking)'[Date Periods])
VAR selectedcustomdates = SELECTEDVALUE('DIM Calendar (Ranking)'[Date])

VAR FixedPreviousMonth=
CALCULATE(
    [Vol],
    FILTER(ALL('DIM Date Filter (Dealer Ranking)'),'DIM Date Filter (Dealer Ranking)'[Date] >= mindate && 'DIM Date Filter (Dealer Ranking)'[Date] <= maxdate))

VAR CustomPreviousMonth=
CALCULATE(
    [Vol],
    FILTER(ALL('DIM Calendar (Ranking)'),'DIM Calendar (Ranking)'[Date] >= mindate && 'DIM Calendar (Ranking)'[Date] <= maxdate))
RETURN

SWITCH(
    TRUE(),
    selectedfixedates =selectedfixedates,FixedPreviousMonth,
    selectedcustomdates = selectedcustomdates, CustomPreviousMonth, blank())
 
mom ranking1 =

VAR fixeddates = SELECTEDVALUE('DIM Date Filter (Dealer Ranking test))'[Date])
VAR customdates = SELECTEDVALUE('DIM Calendar (Ranking)'[Date])

VAR fixedranking =
    IF(
        [Dealerrabk] - [Last m ranking] < 0,
        ABS([Dealerrabk] - [Last m ranking]),
        ABS([Dealerrabk] - [Last m ranking]))

VAR customranking =
    IF(
        [Dealerrabk] - [Last m ranking]< 0,
        ABS([Dealerrabk] - [Last m ranking]),
      ABS( [Dealerrabk] - [Last m ranking])
    )

RETURN
    SWITCH(
        TRUE(),
        fixeddates = fixeddates, fixedranking,
        customdates = customdates, customranking,
        BLANK()
    )
 
Last Quar Ranking Vol =
 VAR maxdate = EOMONTH(TODAY(), -1)
VAR mindate = EOMONTH(TODAY(), -5) + 1
VAR fixeddate = SELECTEDVALUE('DIM Date Filter (Dealer Ranking)'[Date Periods])
VAR customdate = SELECTEDVALUE('DIM Calendar (Ranking)'[Date])

VAR fixvol =
    CALCULATE(
        [Vol],
        FILTER(
            ALL('DIM Date Filter (Dealer Ranking)'),
            'DIM Date Filter (Dealer Ranking)'[Date] >= mindate &&
            'DIM Date Filter (Dealer Ranking)'[Date] <= maxdate
        )
    )

VAR custvol =
    CALCULATE(
        [Vol],
        FILTER(
            ALL('DIM Calendar (Ranking)'),
            'DIM Calendar (Ranking)'[Date] >= mindate &&
            'DIM Calendar (Ranking)'[Date] <= maxdate
        )
    )

RETURN
    SWITCH(
        TRUE(),
        fixeddate = fixeddate, fixvol,
        customdate = customdate, custvol,
        BLANK()
    )

Last Quar ranking =

VAR maxdate = EOMONTH(TODAY(), -1)
VAR mindate = EOMONTH(TODAY(), -5) + 1
VAR checkfixed = SELECTEDVALUE('DIM Date Filter (Dealer Ranking)'[Date Periods])
VAR checkcustom = SELECTEDVALUE('DIM Calendar (Ranking)'[Date])
VAR fixedrank =
    CALCULATE(
        [Dealerrabk],
        FILTER(
            ALL('DIM Date Filter (Dealer Ranking)'),
            'DIM Date Filter (Dealer Ranking)'[Date] >= mindate &&
            'DIM Date Filter (Dealer Ranking)'[Date] <= maxdate
        )
    )
VAR customrank =
    CALCULATE(
        [Dealerrabk],
        FILTER(
          ALL(  'DIM Calendar (Ranking)'),
            'DIM Calendar (Ranking)'[Date] >= mindate &&
            'DIM Calendar (Ranking)'[Date] <= maxdate
        )
    )
RETURN
    SWITCH(
        TRUE(),
        checkfixed = checkfixed, fixedrank,
        checkcustom = checkcustom, customrank,
        BLANK()
    )
 
QoQ Rank (Fixed) =
VAR fixeddates = SELECTEDVALUE('DIM Date Filter (Dealer Ranking test))'[Date])
VAR customdates = SELECTEDVALUE('DIM Calendar (Ranking)'[Date])
VAR lastQuarterRanking = [Last Quar ranking]
VAR dealerank = [Dealerrabk]
VAR fixedranking =
    IF (
        dealerank - lastQuarterRanking < 0,
        (dealerank - lastQuarterRanking ) * -1,
        dealerank - lastQuarterRanking
    )
VAR customranking =
    IF (
        dealerank - lastQuarterRanking < 0,
        ( dealerank - lastQuarterRanking ) * -1,
        dealerank - lastQuarterRanking
    )
RETURN
    SWITCH (
        TRUE (),
        fixeddates = fixeddates, fixedranking,
        customdates = customdates, customranking,
        BLANK ()
    )
 
 
 
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

ViralPatel212 

 

to know which measures are affecting the performance, 

you can create a table per each measure and check from the performance analyser , each visual how much its dax code it taking. 

this way we can filter out all measures that are dont affect performance. .

 

can you please do it, to know which measures needs improvements ( if the improvement is possible ) 

 

 

  • ViralPatel212's avatar
    ViralPatel212
    Resolver I
    2 years ago

    Daniel29195 

    Thanks for the quick response: 

    Here is the results: 

    LastM Dax 801
    Last M Ranking Dax: 903ms
    Mom Ranking 1 Dax: 2313ms
    Last Quar Ranking Vol Dax: 447ms
    Last Quar ranking Dax: 730ms
    QoQ Ranking (Fixed) Dax 918ms