Forum Discussion
Help needed for DAX solution
Hi,
I'm facing a challenge on a DAX measure.
Sample data as below with 3 tables (Fact, dim_week, dim_cust). I will calculate the Best Week of each customer 1st, it can be hardcoded as below (in real use case, this must be a measure to calculate based on user selected filter context e.g. by product). Then, i need to calculate the count of customer by Best Week and link back to dim_week.
Best Week =
VAR cust = SELECTEDVALUE(dim_cust[Customer], "All")
RETURN SWITCH(TRUE(), cust = "c1", "w3", cust = "c2", "w2", cust = "c3", "w2", BLANK())
I tried to use TreatAs but it doesn't work for virtual table. Any help is appreciated.
Customer Count =
VAR t1 = SUMMARIZE(dim_cust, dim_cust[Customer], "Best Week", CALCULATE([Best Week], ALL(dim_week))) -- need to overwrite dim_week context with All
RETURN CALCULATE(COUNTX(t1, [Customer]), TREATAS(t1[Best Week], dim_week[Week]))
| Fact table | dim_week | dim_cust | Best Week | Expected Output | ||||||||
| Week | Customer | Amount | Week | Customer | Customer | Best Week | Week | Customer Count | ||||
| w1 | c1 | 10 | w1 | c1 | c1 | w3 | w2 | 1 | ||||
| w2 | c1 | 20 | w2 | c2 | c2 | w2 | w3 | 2 | ||||
| w3 | c1 | 30 | w3 | c3 | c3 | w2 | ||||||
| w1 | c2 | 20 | ||||||||||
| w2 | c2 | 30 | ||||||||||
| w3 | c2 | 10 | ||||||||||
| w1 | c3 | 20 | ||||||||||
| w2 | c3 | 30 | ||||||||||
| w3 | c3 | 10 |
Thanks, Di
Anonymous try following, I'm sure there is going to be another elegant way but don't have energy right now to look for it, but I think for now this will do.
Customer Count by Week = VAR _customerWeek = SUMMARIZE( Customer, Customer[Customer], "Week", [Best Week] ) RETURN CALCULATE( DISTINCTCOUNT( Sales[Customer] ), INTERSECT( SELECTCOLUMNS( Sales, "Customer", Sales[Customer], "Week", [Week] ), __customerWeek ) )parry2k Excellent, it works. Thank you very much.
6 Replies
Anonymous you need to change your measure to column
Best Week as Col = VAR cust = CALCULATE( SELECTEDVALUE(customer[Customer], "All") ) RETURN SWITCH(TRUE(), cust = "c1", "w3", cust = "c2", "w2", cust = "c3", "w2", BLANK())
and now everything is super easy, I'm sure you can take it from here.
Anonymous not necessarily you have to make it measure even if it calculated on fly, don't get lost into that. If you can give real time example it can be workd out, or you can try it wiht measure you are thinking to use.