The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 fields
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]
@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 )