Speed Issue revisited

[JGPete]

Hi guys Spoke about a speed issue at Christmas time. Now got back to the issue having reworked the model structure somewhat to get rid of some redundant data. I have two visuals on different pages, one using your Financial reporting matric (still awesome BTW) and one using the Microsoft matrix. We'll call these PB matrix and MS matrix.

Each matrix shows Month and YTD amounts for Act, Bud, LY, Bud Variance and LY Variance - so 10 columns of numbers in total.

The MS Matrix works with a Report index and dealership column as row headers with Line description in the values. Last bit of this seems a bit odd, but enables me to put some colour onto it given the weaknesses in the MS Matrix formatting options.

The PB Matrix works with PowerProfitDesc and Dealership in the row header. PowerProfitDesc is of the format {"id":"000006","displayName":"DepartmentExpenses","signFactor":"1","formatString":"#,0;(#,0);"} in order to take advantage of your automated formatting options.

PB Matrix refresh stats are Dax Query 10265ms, Visual Display, 1972ms, Other 1204ms. MS Matrix refresh stats are Dax Query 4,781ms, Visual Display 641ms, Other 122ms.

I have pasted the Dax code for the two refreshes below, but don't understand why PB Matrix is taking so long? Working with v2.0.1 I guess the text string in the column PowerProfitDesc could be the issue but no idea really? Any ideas on how to speed up, given fact table is only 1.6m rows??

Thanks in advance Pete

Dax Code for PB Matrix is as follows: // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('Calendar'[Date])), 'Calendar'[Date] < DATE(2020, 2, 1) )

VAR __DS0FilterTable2 = TREATAS({"GBP 000"}, 'DivBy'[Name])

EVALUATE TOPN( 3002, SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( 'MAReports'[PowerProfitDesc], "IsGrandTotalRowTotal", ROLLUPGROUP('SiteCodes'[Dealership], 'SiteCodes'[Sort_SiteCode]), "IsDM0Total" ), DS0FilterTable, DS0FilterTable2, "MTD_Act", 'Measures - MA'[MTD Act], "MTD_Bud", 'Measures - MA'[MTD Bud], "MTD_Bud_Var", 'Measures - MA'[MTD Bud Var], "MTD_LY", 'Measures - MA'[MTD LY], "MTD_LY_Var", 'Measures - MA'[MTD LY Var], "YTD_Act", 'Measures - MA'[YTD Act], "YTD_Bud", 'Measures - MA'[YTD Bud], "YTD_Bud_Var", 'Measures - MA'[YTD Bud Var], "YTD_LY", 'Measures - MA'[YTD LY], "YTD_LY_Var", 'Measures - MA'[YTD LY Var] ), [IsGrandTotalRowTotal], 1, 'MAReports'[PowerProfitDesc], 1, [IsDM0Total], 1, 'SiteCodes'[Sort_SiteCode], 1, 'SiteCodes'[Dealership], 1 )

ORDER BY [IsGrandTotalRowTotal], 'MAReports'[PowerProfitDesc], [IsDM0Total], 'SiteCodes'[Sort_SiteCode], 'SiteCodes'[Dealership]

Dax Code for the MS Matrix is: // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('Calendar'[Date])), 'Calendar'[Date] < DATE(2020, 2, 1) )

VAR __DS0FilterTable2 = TREATAS({"GBP 000"}, 'DivBy'[Name])

VAR DS0Core = SUMMARIZECOLUMNS( 'MAReports'[Index], DS0FilterTable, __DS0FilterTable2, "MinDescription", CALCULATE(MIN('MAReports'[Description])), "MTD_Bud_Var", 'Measures - MA'[MTD Bud Var], "MTD_LY_Var", 'Measures - MA'[MTD LY Var], "YTD_LY_Var", 'Measures - MA'[YTD LY Var], "YTD_Bud_Var", 'Measures - MA'[YTD Bud Var], "MTD_Bud", 'Measures - MA'[MTD Bud], "MTD_LY", 'Measures - MA'[MTD LY], "YTD_Act", 'Measures - MA'[YTD Act], "YTD_Bud", 'Measures - MA'[YTD Bud], "YTD_LY", 'Measures - MA'[YTD LY], "MTD_Act", 'Measures - MA'[MTD Act], "MinRowBackColor", IGNORE(CALCULATE(MIN('MAReports'[RowBackColor]))), "MinRowFontColor", IGNORE(CALCULATE(MIN('MAReports'[RowFontColor]))) )

EVALUATE GROUPBY( __DS0Core, "MinYTD_LY_Var", MINX(CURRENTGROUP(), [YTD_LY_Var]), "MaxYTD_LY_Var", MAXX(CURRENTGROUP(), [YTD_LY_Var]) )

EVALUATE TOPN(501, __DS0Core, 'MAReports'[Index], 1)

ORDER BY 'MAReports'[Index]

[toresenneseth]

Hi, sorry for the late response,

Not sure if I can be of much help here, without access to the actual data source. Have you measured the differences when running the queries directly (not from Power BI)?

[JGPete]

Hi guys Ran queries in dax studio and had similar timings. Thinking the problem might be to do with a hash table for PowerProfitDesc({"id":"000006","displayName":"DepartmentExpenses","signFactor":"1","formatString":"#,0;(#,0);"}), I took this out amd replicated AccountKey and Desc in the PB matrix so that the comparison between thetwo matrixes was similar. This gave similar timings. So very puzzled. Happy to share PBIX if that helps, but need a secure way of getting it to you?

Pete