Forum Discussion
Help needed for DAX 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 ) )parry2k Excellent, it works. Thank you very much.
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
)
)parry2k Excellent, it works. Thank you very much.