how to understand effect of crossfilter()

[jj_0511]

Hi, 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:

Date	related date in 2019
2-4-2023	2-9-2019
2-5-2023	2-10-2019
2-6-2023	2-11-2019
2-7-2023	2-12-2019
2-8-2023	2-13-2019
2-9-2023	2-14-2019
2-10-2023	2-15-2019

Sales sample data:

date	store	sales
2/11/2019	A	4
2/14/2019	A	7
2/11/2019	B	2
2/14/2019	B	6
2/11/2019	C	7
2/14/2019	C	3

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:

1. Why is crossfilter() is needed in the measure? Shouldn’t the other relationship be deactivated when userelationship() is in place?
2. When crossfilter() is not used, how is the value of 32 calculated?

Please find pbix here: pbix  Thanks.

[lbendlin]

USERELATIONSHIP  only switches an inactive connection to be active temporarily, it doesn't change the connection's filter direction.  CROSSFILTER decides if the standard filtering direction should be modified (ie which table controls the other). CROSSFILTER,,NONE  basically renders the connection useless and makes it into a crossjoin.

[jj_0511]

@lbendlin Thanks, but when USERELATIONSHIP( 'Date'[related date in 2019] , Sales[date] ) is used, the relationship between 'Date'[Date] and Sales[date] is deactivated, right? Since only one relationship can be active at any time. In that case, why do we still need CROSSFILTER( 'Date'[Date] , Sales[date] , None )?

Also, When crossfilter() is not used, how is the value of 32 calculated?

[lbendlin]

 the other is many to many between ‘Date’[related date in 2019] and Sales[date]

I didn't catch this part.  You are not supposed to switch a column in a fact table between two columns in a dimension table. You are supposed to switch a column in the dimension table between two columns in a fact table, like Order Date and Ship Date.

Use TREATAS to project weird filters between unrelated tables.