Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 rea...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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  
    )
)
  • Anonymous's avatar
    Anonymous
    7 years ago

    parry2k Excellent, it works. Thank you very much. 

parry2k's avatar
parry2k
Super User
7 years ago

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.