Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ViralPatel212
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION

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

 

 

View solution in original post

4 REPLIES 4
Daniel29195
Super User
Super User

@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-...

 


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

 

 

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

ViralPatel212_0-1707483119836.png

 

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

@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 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.