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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
ChrisJC
Helper I
Helper I

Troubleshooting Performance of a Visual

I'm fairly new to PowerBI and am trying to optimise a report I've been given. It uses direct query.

I have a particular page that is performing poorly.

I have isolated a specific visual that is the worst offender.

I have tested the measures used and while they could improve, individually they don't seem too bad (a couple of seconds). However the visual as a whole takes around 30s to render.

 

When I examine what's happening in DAX Studio there's lot that I can't see how it relates to the visual but that's probably just me.

 

Is it the case that the way the visual loads the data, each measure, while only taking a couple of seconds to load, is re-applied so many times it's multipled out? Any pointers on where I should focus my efforts would be greatly appreciated.

 

Here is the visual with the fieldspowerbichart.JPG

and here is the output from DAX Studio:

DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[DayName])),
      NOT('Calendar'[DayName] IN {"Saturday",
        "Sunday"})
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'User'[ConsultantName],
      ROLLUPADDISSUBTOTAL('Job'[HasShortlist], "IsGrandTotalColumnTotal"),
      __DS0FilterTable,
      "Count_Jobs_Pipeline_Future", 'Σ Job Measures'[Count Jobs Pipeline Future],
      "Sum_Job_Revenue_Pipeline_Future", IGNORE('Σ Revenue Measures'[Sum Job Revenue Pipeline Future])
    )

  VAR __DS0CoreOnlyOutputTotals = 
    SELECTCOLUMNS(
      KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalColumnTotal] = FALSE)),
      "'User'[ConsultantName]", 'User'[ConsultantName],
      "'Job'[HasShortlist]", 'Job'[HasShortlist],
      "Count_Jobs_Pipeline_Future", [Count_Jobs_Pipeline_Future],
      "Sum_Job_Revenue_Pipeline_Future", [Sum_Job_Revenue_Pipeline_Future]
    )

  VAR __DS0CoreTableByDM0 = 
    SELECTCOLUMNS(
      KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalColumnTotal] = TRUE)),
      "'User'[ConsultantName]", 'User'[ConsultantName],
      "SortBy_DM0_0", [Count_Jobs_Pipeline_Future]
    )

  VAR __DS0PrimaryWithSortColumns = 
    NATURALLEFTOUTERJOIN(
      SUMMARIZE(__DS0Core, 'User'[ConsultantName]),
      __DS0CoreTableByDM0
    )

  VAR __DS0Primary = 
    TOPN(201, __DS0PrimaryWithSortColumns, [SortBy_DM0_0], 0, 'User'[ConsultantName], 1)

  VAR __DS0Secondary = 
    TOPN(62, SUMMARIZE(__DS0CoreOnlyOutputTotals, 'Job'[HasShortlist]), 'Job'[HasShortlist], 1)

EVALUATE
  __DS0Secondary

ORDER BY
  'Job'[HasShortlist]

EVALUATE
  NATURALLEFTOUTERJOIN(
    __DS0Primary,
    SUBSTITUTEWITHINDEX(
      __DS0CoreOnlyOutputTotals,
      "ColumnIndex",
      __DS0Secondary,
      'Job'[HasShortlist],
      ASC
    )
  )

ORDER BY
  [SortBy_DM0_0] DESC, 'User'[ConsultantName], [ColumnIndex]
4 REPLIES 4
parry2k
Super User
Super User

@ChrisJC did you used performance analyzer to pin point the issue

 

https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k,

 

Thanks for the pointer, I don't know how I haven't seen that before. Is it a new feature?

 

However, in this instance it doesn't tell me anything new:

DAX Query is 24.5s out of 25.4 total load.

The Copy Query gives my the SQL commands same as DAX Studio.

 

Playing around further it looks like the Value field "Jobs" is the one causing the issue. When I put this in a card on it's own it's quick so it must be the filters that are being applied in the visual?

 

Here is the measure definition with the measures that are nested

 MEASURE 'Σ Job Measures'[Count Jobs Pipeline Future] = VAR MaxSysDate =
            MAX (
                MAX (
                    CALCULATE ( MAX ( Job[CreatedDate] ), ALL ( Job ) ),
                    CALCULATE ( MAX ( Placement[PlacedDate] ), ALL ( Placement ) )
                ),
                CALCULATE ( MAX ( Event[EventDate] ), ALL ( Event ), ALL ( EventCandidate ) )
            )
        RETURN
            CALCULATE (
                COUNTROWS ( 'Job' ),
                FILTER (
                    Job,
                    CALCULATE (
                        ROUND ( [Sum Job Revenue PermFixed] + SUMX( 'Calendar', [Sum Job Revenue Contract Daily] ) + [Sum Job Revenue Temp Unfilled Places]
                        , 0 ),
                        VALUES ( Job[Job Status Type] ),
                        Job[Job Status Type] = "Current",
                        JobFilledBy[FilledById] = BLANK (),
                        // Job places that haven't been filled by US or anyone else
                        FILTER( ALL('Calendar'), 'Calendar'[Date] >= MaxSysDate)
                    ) <> 0
                ),
                VALUES ( Job[HasShortlist] ),
                VALUES ( 'Calendar'[DayName] )
            )

MEASURE 'Σ Revenue Measures'[Sum Job Revenue PermFixed] = CALCULATE (
    SUM( 'JobFilledPlacesRevenue'[MinSalaryFeeValue] ),
    FILTER (
        'JobFilledPlacesRevenue',
        'JobFilledPlacesRevenue'[Start Date] <= MAX( 'Calendar'[Date] )
        && 'JobFilledPlacesRevenue'[Start Date] >= MIN( 'Calendar'[Date] )
    )
)
MEASURE 'Σ Revenue Measures'[Sum Job Revenue Contract Daily] = CALCULATE (
    SUM( 'JobFilledPlacesRevenue'[RevenuePerPlacePerDay] ),
    FILTER (
        'JobFilledPlacesRevenue',
        'JobFilledPlacesRevenue'[Start Date] <= MAX( 'Calendar'[Date] )
            && 'JobFilledPlacesRevenue'[End Date] >= MIN ( 'Calendar'[Date] )
    )
)
MEASURE 'Σ Revenue Measures'[Sum Job Revenue Temp Unfilled Places] = CALCULATE(
    ROUND( SUMX( JobShiftWithRates, [ShiftProfit]*[Unfilled Places] ), 0 ),
    USERELATIONSHIP( Job[Postcode], Postcode[Postcode] )
)

As some of the child measures use the same filters, can this be tidied up so it's only applied once?

 

Thanks,

Chris

Is there any way of cleaning up the DAX to making it perform better? It's currently taking around 8 seconds in DAX Studio to run with the basic filters from the report

I've gone back to the SQL and distilled what it's doing. How do I start writing this in DAX?

 

@lastdate will be a filter on the report

SELECT COUNT(DISTINCT j.[jobid])
FROM   Job j
       LEFT JOIN JobRevenue r
              ON j.jobid = r.jobid
       LEFT JOIN ShiftRevenue s
              ON j.jobid = s.jobid
WHERE  j.[job status type] = 'Current'
       AND ( ( r.[filled by] IS NULL
              AND ( ( r.minsalaryfeevalue > 0 AND j.[job type] = 'Perm' AND r.[start date] > @lastdt )
                      OR ( r.revenueperplaceperday > 0 AND j.[job type] = 'Contract' AND r.[end date] > @lastdt ) ) )
              OR ( s.shiftprofit > 0 AND s.[unfilled places] > 0 AND j.[job type] = 'Temp' AND s.[report date] > @lastdt ) 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors