Help with Filter Context using USERELATIONSHIP on Dates

DAX DAX Calculations
1

Can anyone help me understand why the results of this DAX formula takes into account both date fields in my matrix visual when I’m using USERELATIONSHIP ? My goal is to show the Principal based on only the Cancellation Date, and ignore the Issued Date.
My two measures are:

Principal = 
VAR _result =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Lifecycle',
            'Lifecycle'[Number]
        ), "@PB", CALCULATE( MAX('Lifecycle'[Principal]), USERELATIONSHIP( 'Lifecycle'[StatusDate], 'Date Table'[Date] ) )
    ), [@PB]
)
RETURN
_result

Cancellation Date = 
VAR _result =
CALCULATE (
    SELECTEDVALUE ( 'Date Table'[Date] ),
    CALCULATETABLE (
        'Lifecycle',
        USERELATIONSHIP ( 'Lifecycle'[StatusDate], 'Date Table'[Date] )
    )
)
RETURN
_result

image
image1452×530 96.1 KB

As you can see, the principal measure appears only when numbers have an issued date and cancellation date within the Date Filter.
Below is the matrix when I select All Dates - removing the Date filter:

image
image1458×678 163 KB

Seems like a simple fix but can’t wrap my head around what is happening with the filter context.

Any insight will be much appreciated!

2

@Schreg,

Can you share a pbix file? If not, what does the relationship view look like? It’s important to know especially if relationships are bidirectional or if multiple, active relationships exist between the same tables.

3

@HufferD I appreciate the quick reply!
here is the active relationship:

image
image699×434 22.2 KB

and here is the inactive relationship:
image
image699×449 22.8 KB

*“Report Date” in relationship = “issued date” in matrix, I changed the name on the visual.

4

The short answer to your original question,

is that your date slicer filters Date Table, and, because you have active and inactive relationships to that table from Lifecycle, it affects both Report Date and StatusDate indirectly.

Which of your dates do you intend the date slicer to affect? Report Date or is it meant to filter on both dates?

5

the date filter is meant to filter the Status Date only

6

Thanks for clarifying. The slicer isn’t doing what you want.

In this context, using USERELATIONSHIP to make Lifecycle[StatusDate] the active relationship temporarily within the DAX measure is correct. However, since the date slicer is connected to the Date Table, it will impact both Lifecycle[Report Date] and Lifecycle[StatusDate] due to the way relationships are set up, which isn’t what you want.

One option is to create a disconnected date table that contains just the columns you need to slice by ‘Status Date’. This table would not have relationships with other tables in your model. You could then create a slicer based on this disconnected table and use DAX to control your ‘Principal’ measure calculation based on the selected values of the disconnected date table.

This approach gives you a lot of control and avoids any conflicts with your existing relationships and active filters.

7

Thanks for the suggestions @HufferD , could you show the DAX that would control the ‘Principle’ measure calculation based on the selected values of the disconnected date table?