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:
VAR _result =
), "@PB", CALCULATE( MAX('Lifecycle'[Principal]), USERELATIONSHIP( 'Lifecycle'[StatusDate], 'Date Table'[Date] ) )
Cancellation Date =
VAR _result =
SELECTEDVALUE ( 'Date Table'[Date] ),
USERELATIONSHIP ( 'Lifecycle'[StatusDate], 'Date Table'[Date] )
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:
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!
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.
@HufferD I appreciate the quick reply!
here is the active relationship:
and here is the inactive relationship:
*“Report Date” in relationship = “issued date” in matrix, I changed the name on the visual.
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
Which of your dates do you intend the date slicer to affect?
Report Date or is it meant to filter on both dates?
the date filter is meant to filter the Status Date only
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.
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?