Visual loading Slow

[ViralPatel212]

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

[Daniel29195]

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

[ViralPatel212]

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

[ViralPatel212]

@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