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 bring the duration lower:

 

Here is the dax code copied from performance analyser:

// DAX Query
DEFINE
VAR __DS0FilterTable = 
FILTER(
KEEPFILTERS(VALUES('Dealer Ranking Tbl'[CounterParty2])),
NOT('Dealer Ranking Tbl'[CounterParty2] IN {BLANK(),
""})
)
 
VAR __DS0FilterTable2 = 
TREATAS({"All"}, 'DIM Date Filter'[Date Periods])
 
VAR __DS0FilterTable3 = 
TREATAS({"ALL (EUR)"}, 'Dealer Ranking Tbl'[Currency])
 
VAR __ValueFilterDM0 = 
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Dealer Ranking Tbl'[CounterParty2],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Dealerrabk", 'Dealer Ranking Tbl'[Dealerrabk],
"Vol", 'Dealer Ranking Tbl'[Vol],
"Trades", 'Dealer Ranking Tbl'[Trades],
"Last_m_ranking", 'Dealer Ranking Tbl'[Last m ranking],
"mom_ranking1", 'Dealer Ranking Tbl'[mom ranking1],
"Last_Quar_ranking", 'Dealer Ranking Tbl'[Last Quar ranking],
"QoQ_Rank__Fixed_", 'Dealer Ranking Tbl'[QoQ Rank (Fixed)],
"LastM_Vol", 'Dealer Ranking Tbl'[LastM Vol],
"Last_Quar_Ranking_Vol", 'Dealer Ranking Tbl'[Last Quar Ranking Vol],
"Last_month_Ranking_Change1", IGNORE('Dealer Ranking Tbl'[Last month Ranking Change1]),
"white_text", IGNORE('Dealer Ranking Tbl'[white text]),
"Last_Quarter_Ranking_Change1", IGNORE('Dealer Ranking Tbl'[Last Quarter Ranking Change1])
)
),
NOT(ISBLANK([Vol]))
)
 
VAR __DS0Core = 
SUMMARIZECOLUMNS(
'Dealer Ranking Tbl'[CounterParty2],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__ValueFilterDM0,
"Dealerrabk", 'Dealer Ranking Tbl'[Dealerrabk],
"Vol", 'Dealer Ranking Tbl'[Vol],
"Trades", 'Dealer Ranking Tbl'[Trades],
"Last_m_ranking", 'Dealer Ranking Tbl'[Last m ranking],
"mom_ranking1", 'Dealer Ranking Tbl'[mom ranking1],
"Last_Quar_ranking", 'Dealer Ranking Tbl'[Last Quar ranking],
"QoQ_Rank__Fixed_", 'Dealer Ranking Tbl'[QoQ Rank (Fixed)],
"LastM_Vol", 'Dealer Ranking Tbl'[LastM Vol],
"Last_Quar_Ranking_Vol", 'Dealer Ranking Tbl'[Last Quar Ranking Vol],
"Last_month_Ranking_Change1", IGNORE('Dealer Ranking Tbl'[Last month Ranking Change1]),
"white_text", IGNORE('Dealer Ranking Tbl'[white text]),
"Last_Quarter_Ranking_Change1", IGNORE('Dealer Ranking Tbl'[Last Quarter Ranking Change1])
)
 
VAR __DS0PrimaryWindowed = 
TOPN(501, __DS0Core, [Dealerrabk], 1, 'Dealer Ranking Tbl'[CounterParty2], 1)
 
EVALUATE
__DS0PrimaryWindowed
 
ORDER BY
[Dealerrabk], 'Dealer Ranking Tbl'[CounterParty2]

 

 thanks

Viral

 

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 ) 

     

     

4 Replies

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