Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Unfortunately, [Best Week] must be a measure because it needs to be calculated within filter context on the fly.
Any other suggestion. Thanks.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k thanks for the advice.
But in my real use case, Best Week need to re-calculate based on user selection/filter context e.g. product, program, country and I do need to take care time horizon for Best week so on, the real logic is quite complicated. It's not relistic to create it as column and to re-process/re-calc the model. Hence, i need a measure and also need to use virtual table when calculate Customer Count. What I post here is super simplified use case but focus on the Customer Count problem.
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |