How is crossfilter() affecting this measure calculation in Power BI?

I have two tables: Date and Sales

Date:

Values in Date column is unique, values in “related date in 2019” is not unique.

Sample data:

Sales sample data:

There are 2 relationships defined between Date and Sales. One is one to many between 'Date'[Date] and Sales[date]; the other is many to many between ‘Date’[related date in 2019] and Sales[date]

WTD sales 2019 for 2/9/2023 is calculated as:

For 2/9/2023, WTD dates are 2/6/2023 - 2/9/2023. The corresponding dates in 2019 for these dates based on ‘Date’[related date in 2019] are 2/11/2019 to 2/14/2019. As a result, WTD sales 2019 for 2/9/2023 is calculated as total sales for 2/11/2019 to 2/14/2019.

I have following measure:

Sales WTD 2019 =
VAR firstDayofWeek =
    SELECTEDVALUE ( 'Date'[Date] ) - WEEKDAY ( SELECTEDVALUE ( 'Date'[Date] ), 2 ) + 1
VAR CurrentDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[sales] ),
        DATESBETWEEN ( 'Date'[Date], firstDayofWeek, currentdate ),
        USERELATIONSHIP ( 'Date'[related date in 2019], Sales[date] ) 
        //, CROSSFILTER( 'Date'[Date] , Sales[date] , None )
    )

When CROSSFILTER( 'Date'[Date] , Sales[date] , None ) is NOT used, WTD sales 2019 for 2/9/2023 is 32 which is incorrect.

When CROSSFILTER( 'Date'[Date] , Sales[date] , None ) is used, WTD sales 2019 for 2/9/2023 is 29 which is correct.

My questions are:

Why is CROSSFILTER needed in the measure? Shouldn’t the other relationship be deactivated when USERELATIONSHIP is in place?
When CROSSFILTER is not used, how is the value of 32 calculated?

Please find pbix here:
https://drive.google.com/file/d/1Fls716yz01FgIqmQ6JVHNmDBbuy8R07k/view?usp=drive_link

Thanks.

Wow, this was a really interesting question!

How is 2023-02-09 calculated when CROSSFILTER is NOT applied?

I believe the calculation is not as you expect it to be, because while you are using USERELATIONSHIP to transplant the active relationship, you are also using the primary/active relationship column in the DAX expression to establish the filter context. By doing this, I believe you are persisting the use of the active relationship. In other words, USERELATIONSHIP will not be able transplant the active relationship as the calculation is set up. This behavior is alluded to in the documentation (emphasis mine):

> In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.

Hence, for your 2023-02-09 you are simply summing the sales amount over the period 2023-02-06 to 2023-02-09, which turns out to be 32.

How is 2023-02-09 calculated when CROSSFILTER is applied?

When you explicitly turn off the primary relationship, the filter context becomes what you expect, since you are removing the option of using the primary relationship altogether, so to speak.

Are there other ways to solve this?

If you are not comfortable with using both CROSSFILTER and USERELATIONSHIP in the same expression (this can be explained in a comment, obviously, but may still be difficult to grasp?), you can opt for something like this instead, where we first calculate the appropriate 2019 dates using VALUES, and use TREATAS to propagate the filter context to sales:

Sales WTD 2019 =
VAR _current_date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _first_day_of_week =
    _current_date - WEEKDAY ( _current_date, 2 ) + 1
VAR _dates =
    CALCULATETABLE (
        VALUES ( 'Date'[related date in 2019] ),
        DATESBETWEEN ( 'Date'[Date], _first_day_of_week, _current_date )
    )
VAR _out =
    CALCULATE (
        SUM ( Sales[sales] ),
        ALL ( Sales ),
        TREATAS ( _dates, Sales[sales_date] )
    )
RETURN
    _out

I was able to download your pbix:

So, if CROSSFILTER is not used, it seems the active relationship remains the default 1:n between Date[Date] and Sales[date]

In that case, for the 2/9/2023, you will get the 17+15 = 32 from that week (2/6 and 2/9)

Interestingly, if you change the default active relationship to the m:n with bi-directions between Date[related date in 2019] and Sales[date], the USERELATIONSHIP works without CROSSFILTER, i.e., applying USERELATIONSHIP to switch to the deactivated 1:n works without CROSSFILTER

Edit:

I also noticed a potential issue with your measure with the week around 1/1/2023 and 12/31/2022. There is a “jump” from 1/6/2019 to 1/4/2020 in the [related date in 2019]. So the calculation in this case sums up values with a year gap.

Alternatively, if applicable, might be easier just to calculate the offset to 2019 in your measure instead of using the second column and avoiding the second relationship with m:n

Edit 2 (6/6):

I just did one more thing: I added another column Date[related date in 2019 distinct] with distinct dates and defined a third, deactivated 1:n relation to Sales[date]

In that case the measure works just fine too, i.e., having the default, active 1:n between Date[Date] and Sales[date], using USERELATIONSHIP to switch to the 1:n relation between Date[related date in 2019 distinct] and Sales[date] produces the expected result without the presence of CROSSFILTER

Brief Summary

• in your setup, USERELATIONSHIP of the m:n connection does not work without additional CROSSFILTER and the active 1:n connection prevails
• on the other hand, using USERELATIONSHIP to switch to another 1:n connection works as expected (w/o the need of CROSSFILTER)

Workaround

• change your model setup to have the m:n connection active and to switch to the 1:n with USERELATIONSHIP (w/o CROSSFILTER) instead
• use a formula to calculate the date offset instead of the second date column avoiding m:n (my personal preference)
• proposal from @Marcus: use virtual table and TREATAS