Sales Amount per day for top 1 customer performance issue

[Anonymous]

Hello,

I am looking to get for each day the Sales Amount for the biggest client and check how much it contributes to the total Sales. I did this code that work. This code also includes a logic to get the previous day if there isn't any data for a given day but it's very slow. Link is to a dev environment with the query.

https://dax.do/xsmPgtFoM6lbkL/

DEFINE
MEASURE Sales[Sales Amount Prev] =
VAR LastDateNon =
CALCULATE (
MAX ( 'Date'[Date] ),
filter(ALL('Date'[Date]),
DATEDIFF('Date'[Date],SELECTEDVALUE( 'Date'[Date] ),DAY)<5
&& 'Date'[Date] < SELECTEDVALUE( 'Date'[Date] )
&&NOT ISBLANK ([Sales Amount]))
)
VAR result =
CALCULATE ( [Sales Amount], 'Date'[Date] = LastDateNon )
RETURN
if ([Sales Amount]=BLANK (),result,[Sales Amount])

measure Sales[Top1CustomerWithPrev]=
MAXX ( VALUES ( Customer[Company Name]), Sales[Sales Amount Prev] )
measure Sales[Top1Customer]=
MAXX ( ALL(Customer[Company Name]), Sales[Sales Amount Prev] )
measure Sales[Percentage top 1 Customer]=
Sales[Top1Customer]/[Sales Amount]

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Date] ,
-- Customer[Company Name],
FILTER (All('Date'[Date]),'Date'[Date] >= DATE ( 2007, 2, 7 ) && 'Date'[Date] <= DATE ( 2008, 2, 16 ))
,"Total Sales Amount", [Sales Amount]
-- ,"Previous day", Sales[Sales Amount Prev]
,"Sales Top 1 Customer",Sales[Top1Customer]
,"Percentage best customer over total",Sales[Percentage top 1 Customer]
)
ORDER BY 'Date'[Date]

My issue is with MAXX I can't find any other way but it creates a very slow query on a bigger dataset. Any idea to create a faster query and avoid MAXX ?

Regards

Vincent Diallo-Nort

[lbendlin]

@Anonymous  If only SQLBI had a tool that could help you with query optimization 🙂

Use DAX Studio to evaluate the queries and rearrange your filters so you can avoid/minimize the cartesians and reduce cardinality. Apply the most aggressive filters first.

[Anonymous]

The query is generated by Powerbi therefore i don't have control over it. I was trying to improve the measure inside the Dataset so it's performing well regardless of how the user use it

[lbendlin]

Yes, that's exactly what DAX Studio helps you to do. Give it a try.