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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    Any other suggestion. Thanks.

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.