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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ViralPatel212
Resolver I
Resolver I

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.