cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dming
Advisor

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_weekdim_cust Best Week  Expected Output
WeekCustomerAmount Week Customer CustomerBest Week WeekCustomer Count
w1c110 w1 c1 c1w3 w21
w2c120 w2 c2 c2w2 w32
w3c130 w3 c3 c3w2   
w1c220          
w2c230          
w3c210          
w1c320          
w2c330          
w3c310          

 

Thanks, Di

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Help needed for DAX solution

@dming 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  
    )
)




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




dming
Advisor

Re: Help needed for DAX solution

@parry2k Excellent, it works. Thank you very much. 

6 REPLIES 6
Super User
Super User

Re: Help needed for DAX solution

@dming 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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




dming
Advisor

Re: Help needed for DAX solution

Unfortunately, [Best Week] must be a measure because it needs to be calculated within filter context on the fly. 

Any other suggestion. Thanks.

Super User
Super User

Re: Help needed for DAX solution

@dming 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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




dming
Advisor

Re: Help needed for DAX solution

@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.

Super User
Super User

Re: Help needed for DAX solution

@dming 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  
    )
)




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




dming
Advisor

Re: Help needed for DAX solution

@parry2k Excellent, it works. Thank you very much.