Add a Arrival and Booking Date Slicer with only One Date Table

[jslade]

I have a fact table that has an arrival date key and a booking date key which I have a primary relationship to the date table for the booking date and an inactive relationship to the date table for arrival date key.  I then have a slicer that allows the user to pick how they view the visuals (arrival vs booking) with a date range for the slicer.  I am now being asked to add a slicer for the other date, so they want to be able to slice the data by booking date range with an arrival date range in the future.  Is there a way to do this without having to add another date table? Or how can I tell a date range slicer to use the inactive relationship (I havent' been able to find a way to use the USERELATIONSHIP function as a slicer)?

[jslade]

I'm not sure I follow how I'd use the filtered meaure.  I do have two measures for revenue (one for the booking date and one for arrival date) I then have a measure called revenue that does a switch based on antoher slicer called view by, so the user can view revenue by booking date or by arrival date. I currently only have a date range slicer which is based off the view by slicer.  Are you saying I should replace my revenue by arrival and revenue by booking measures with your filtered measure? Would I have two date slicers, one for arrival and one for booking?

[anilelmastasi · Accepted solution]

Keep your existing revenue measures for Booking and Arrival.

You already have something like this:

Revenue_Booking =
CALCULATE(
SUM(FactTable[Revenue]),
USERELATIONSHIP(FactTable[BookingDateKey], Dim_Date[Date])
)

Revenue_Arrival =
CALCULATE(
SUM(FactTable[Revenue]),
USERELATIONSHIP(FactTable[ArrivalDateKey], Dim_Date[Date])
)

Revenue =
SWITCH(
SELECTEDVALUE('Slicer_ViewBy'[View By]),
"Booking", [Revenue_Booking],
"Arrival", [Revenue_Arrival]
)

Keep these as they are! These measures work well for your single date slicer setup.

Add a second date slicer (one for Booking Date, one for Arrival Date).

-Currently, your slicer is based on only one date (either Booking Date or Arrival Date, depending on the "View By" slicer).
-To allow filtering by both dates, you need two slicers:
One slicer for Booking Date
One slicer for Arrival Date
- Modify your report:

Create two copies of Dim_Date:
Keep Dim_Date (for Booking Date, active relationship).
Create a new disconnected table (Dim_Date_Arrival) for Arrival Date slicer.

Modify your revenue measure to respect both slicers.

Now, instead of only filtering by the active relationship, we need to make sure both date slicers are applied.

Filtered Revenue =
VAR SelectedView = SELECTEDVALUE('Slicer_ViewBy'[View By])

RETURN
    CALCULATE(
        SWITCH(
            SelectedView,
            "Booking", [Revenue_Booking],
            "Arrival", [Revenue_Arrival]
        ),
        -- Apply Booking Date Slicer Filter
        FactTable[BookingDateKey] IN VALUES(Dim_Date[Date]),

        -- Apply Arrival Date Slicer Filter (from the second date table)
        FactTable[ArrivalDateKey] IN VALUES(Dim_Date_Arrival[Date])
    )

[jslade]

Got it; thank you

[anilelmastasi]

Hello @jslade ,

Since slicers cannot directly use inactive relationships, you need to write measures that activate the inactive relationship when needed.

Measure 1: Booking-Based Calculation (Using Active Relationship)
This will work as usual since the relationship is already active.

BookingMeasure = SUM(FactTable[Revenue]) // Or any other metric

Measure 2: Arrival-Based Calculation (Using Inactive Relationship)
Now, use USERELATIONSHIP to activate the Arrival Date relationship dynamically.

ArrivalMeasure =
CALCULATE(
SUM(FactTable[Revenue]),
USERELATIONSHIP(FactTable[ArrivalDateKey], DateTable[Date])
)

Since one slicer (Booking) filters the active relationship, but the other slicer (Arrival) does not inherently work, you need to modify your measure.

Create a new filtered measure that applies both slicer selections:

FilteredMeasure =
CALCULATE(
SUM(FactTable[Revenue]),
USERELATIONSHIP(FactTable[ArrivalDateKey], DateTable[Date]),
FactTable[BookingDateKey] IN VALUES(DateTable[Date])
)