Switch between active and inactive relationships in a measure - 1table&multiple relationships

[Anonymous]

Hi all,

I'd like to check if my understanding is correct, thanks for the help in advance!

I have a date and a fact table between those there are 2 relationships (both 1:N, one-directional):

1) Calendar[Date] --> Fact[Start_Date] (active)

2) Calendar[Date] --> Fact[End_Date] (inactive)

The business requirement is to calculate the number of rows by month where the item in the row is/was "active", so:

If the take 2024 Jan as an example:

All rows should be counted if:

1) Fact[Start_Date] is on or before 31 Jan 2024

2) Fact[End_Date] is on or after 31 Jan 2024 OR Fact[End_Date] is BLANK

A way to calculate this without relying on the relationships is:

v1 =

VAR Last_Date = LASTDATE ( Calendar[Date] )

VAR result = 

CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( Calendar), -- Remove date filers used on the dashboard
    Last_Date  >= Fact[Start_Date],
    
    Last_Date  <= Fact[End_Date]  ||
    Fact[End_Date]   = BLANK()
    
) 

return

result

If I wanted to use the relationship it can be transformed to this:

v2 =

VAR Last_Date = LASTDATE ( Calendar[Date] )

VAR result = 

CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( Calendar), -- Remove date filers used on the dashboard
    Last_Date  >= Calendar[Date], -- this acts as Start_Date
    
    Last_Date  <= Fact[End_Date]  ||
    Fact[End_Date] = BLANK()
    
) 

return

result

Is there any way to create this measure in a way that both the active and the inactive relationships are used?

To me it is a simple requirement but based on my current understanding it cannot be done in DAX. According to my current knowledge it contradicts the way CALCULATE modifyers work. As far as I understand it doesn't metter where I put USERELATIONSHIP to activate the inactive relationship it will transform all my Calendar[Date] to End_Date and I cannot write a measure where in one CALCULATE function I switch between the 2 dates. A similar measure would look like this:

v3 =

VAR Last_Date = LASTDATE ( Calendar[Date] )

VAR result = 

CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( Calendar), -- Remove date filers used on the dashboard
    Last_Date  >= Calendar[Date], -- Start_date
    
    ****[DO SOMETHING HERE to switch from Start to End Date]****
    Last_Date  <= Calendar[Date]  || -- End_Date
    Calendar[Date]   = BLANK()       -- End_Date
    
) 

return

result

So I can ever only use one of the relationships and I need to specifically reference the other columns (write Fact[End_Date] as filter argument) from fact table, even if there's an inactive, potentially usable relationship. Is this assumtipon of mine correct? 

Thanks,  

[Anonymous]

Thanks!

[Greg_Deckler · Accepted solution]

@Anonymous Right, only one relationship can be active at a time. You could create a second Calendar table and link one to Start Date and one to End Date. I made a video on a similar problem here: