Using multiple dates - DAX formula with mulitple inactive relationships

DAX
1

I have a data table that contains multiple dates (Date of Birth, Case Start Date, Case End Date, Removal Date, Discharge Date, Legal Status Start Date, Legal Status End Date, Legal Action Start Date, Legal Action End Date). I have an active relationship between my date table and Case Start Date. I want to calculate the number of unique cases (I have a case reference that is unique) that are open at the end of each month (therefore testing whether the case start date is before the end of the month and Case End Date is blank or less than max date) plus taking into account the Legal Status Dates (i.e. what is their legal status at the end of the month). I have been having difficulty calculating this as it appears that you cannot use USERELATIONSHIP twice in the same calculation since it gives me strange results. Can anybody help me solve this problem?

2

Kevin,

1. Create a Disconnected Date Selection Table

First, create a new table that contains the names of the different date columns as options for selection. You can do this with a static table like:
DateSelector =
DATATABLE(
“Date Type”, STRING,
{
{“Date of Birth”},
{“Case Start Date”},
{“Case End Date”},
{“Removal Date”},
{“Discharge Date”},
{“Legal Status Start Date”},
{“Legal Status End Date”},
{“Legal Action Start Date”},
{“Legal Action End Date”}
}
)


This will create a table called `DateSelector` with one column `Date Type` that contains the names of your date columns.

2. Create a Slicer for the Date Selector

Next, add a slicer to your report that uses the `Date Type` column from the `DateSelector` table. This allows the user to select which date they want to analyze.

 3. Create a Measure to Dynamically Select the Date

Now, you'll need to create a DAX measure that switches between the different date columns based on the user's selection. Here’s how you can do it:

SelectedDate = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(DateSelector[Date Type]) = "Date of Birth", FactTable[DateOfBirth],
    SELECTEDVALUE(DateSelector[Date Type]) = "Case Start Date", FactTable[CaseStartDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Case End Date", FactTable[CaseEndDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Removal Date", FactTable[RemovalDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Discharge Date", FactTable[DischargeDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Legal Status Start Date", FactTable[LegalStatusStartDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Legal Status End Date", FactTable[LegalStatusEndDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Legal Action Start Date", FactTable[LegalActionStartDate],
    SELECTEDVALUE(DateSelector[Date Type]) = "Legal Action End Date", FactTable[LegalActionEndDate]
)

This measure SelectedDate will dynamically return the date from the column that the user has selected in the slicer.

kind regards

Roger

1 Like
3

Was this accidentally posted on the wrong question? It doesn’t appear to be related to the original question.