Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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.
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
Yes, that's exactly what DAX Studio helps you to do. Give it a try.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |