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 ) 

     

     

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

ViralPatel212 

 

please provide some context of the measure.  like what does the measure do ?  how are you using it ? 

etc...

 

 

 

 

 

How to provide sample data in the Power BI Forum
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

 


How to Get Your Question Answered Quickly
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

 

  • 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