topic Re: Switch date table using virtual relationship in DAX Commands and Tips

Switch date table using virtual relationship

mrclay82 — Thu, 17 Aug 2023 07:10:35 GMT

Hi all,

I have a measure called [no of deliveries] which I normally use with my date table ‘Dim-OutboundDate’

I need to use this measure with my other date table ‘Dim-Date’, and the outcome should be the same.

No matter what I try though, the outcome is different when I group it by week in the visual.

This is the new measure I’m trying to create:

CALCULATE(

[no of deliveries],

TREATAS(

VALUES(

'Dim - Date'[ID_Date]

‘Dim-OutboundDate’ [ID_Date]

))

Below is my data model (one to many) and I am restricted to do any physical changes with the relationships, so I need to solve this with a measure.

Many thanks!

Re: Switch date table using virtual relationship

johnt75 — Thu, 17 Aug 2023 09:03:43 GMT

Try

My Measure = CALCULATE ( [no of deliveries], TREATAS ( VALUES ( 'Dim - Date'[ID_Date] ), 'Dim-OutboundDate'[ID_Date] ), REMOVEFILTERS ( 'Dim - Date' ) )

Re: Switch date table using virtual relationship

mrclay82 — Thu, 17 Aug 2023 12:13:49 GMT

It worked! I am really impressed by you sir!
Many thanks!