Help needed for DAX solution

[Anonymous]

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

[parry2k · Accepted 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]

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

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

Any other suggestion. Thanks.

[parry2k]

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

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

[parry2k · Accepted 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  
    )
)

[Anonymous · Accepted solution]

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