cancel
Showing results for
Did you mean:

## 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_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

2 ACCEPTED SOLUTIONS

Accepted Solutions
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
)
)```

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

## Re: Help needed for DAX solution

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

6 REPLIES 6
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.

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

## 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

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

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

## Re: Help needed for DAX solution

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

## 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
)
)```

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