Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to create a filter for a table or matrix such that it only shows customers who have had a change in their top product by sales in the past 3 months.
This is a simplified source table:
Customer | Order Date | Product | Sales |
A | 11/1/19 | 1000A | 12 |
A | 11/1/19 | 1001E | 8 |
A | 10/1/19 | 1000A | 7 |
A | 10/1/19 | 1001E | 10 |
B | 11/1/19 | 1001E | 10 |
B | 10/1/19 | 1001E | 8 |
And the result I'd like would be something like this:
Customer | 3 Months Ago | 2 Months Ago | Last Month | This Month |
A | 1001E | 1001E | 1001E | 1000A |
Customer B is not showing because its top product did not change during the time period. It conditionally formats the month in which the change occurred for easier consumption. If there's a change within 3 months, it passes the filter and highlights the month it changed. Towards this end, I've done some DAX to handle these procedures:
This_Month=LASTDATE(Sales[ORDER DATE])
Minus1=DATEADD(LASTDATE(Sales[ORDER DATE]),-1,MONTH)
Minus2=DATEADD(LASTDATE(Sales[ORDER DATE]),-2,MONTH)
Minus3=DATEADD(LASTDATE(Sales[ORDER DATE]),-3,MONTH)
Top_Product_This_Month = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[This_Month]))
Top_Product_Minus1 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus1]))
Top_Product_Minus2 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus2]))
Top_Product_Minus3 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus3]))
But beyond this point, I have been having constant problems. I've tried scrapping the individual measures and putting them as variables in one big honking measure, but I keep getting errors. Besides, it’s way too clonky to do any additional filtering or manipulation beyond this point. Does anyone have any ideas for a way forward, or a way to do this more efficiently?
I can get the result as below,
I create a calculated column,
month/year = FORMAT([Order Date],"mm-yyyy")
then create measures
m-sales = SUM('Table'[Sales])
rank =
RANKX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[month/year] = MAX ( 'Table'[month/year] )
),
[m-sales],
,
DESC
)
count =
CALCULATE (
COUNT ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
'Table'[Product] = MAX ( 'Table'[Product] )
&& [rank] = 1
&& 'Table'[Order Date] <= MAX ( 'Table'[Order Date] )
&& DATEDIFF ( 'Table'[Order Date], TODAY (), MONTH ) <= 4
)
)
top1 =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
'Table'[month/year] = MAX ( 'Table'[month/year] )
&& 'Table'[rank] = 1
)
)
changetimes =
COUNTX (
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
DATEDIFF ( 'Table'[Order Date], TODAY (), MONTH ) <= 4
&& [count] = 1
),
[count]
)
final =
IF (
DATEDIFF ( MAX ( 'Table'[Order Date] ), TODAY (), MONTH ) <= 4
&& [count] <> BLANK ()
&& [changetimes] >= 2,
[top1]
)
For formatting, it supports to format values instead of "headers".
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting
To format the header, you can change the format of the column header with a "bold" size.
@v-juanli-msft While it appears to work when there are only two customers and two products (looking at your pbix), it absolutely chokes on 8000 customers/10 products and does the little whirly circle for fifteen minutes until it gives a memory error, even fresh off rebooting. Is there any way to make this more efficient so as to work in-situ via a shared report?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |