PowerBI how to use one slicer with two columns (dates)

0
votes

I have a table with columns 'date from' and 'date to', and visualize them as bars using asTimeline visual.

I want to add a slicer which will work on both of these fields simultaneously. Currently I have two slicers working independently on each of those fields:

enter image description here

This is not really intuitive. Since start and end both define a period in time, if the period is inside the slider selection, it should be included. But slicers work on only one field. So I probably need to perform some DAX magic to create a field based on those two, but I don't know where to begin.

2
powerbipowerbi-desktopslicers

2 Answers

1
votes

First create an measure to check a row overlaps the your date range:

Date Included = 
IF (
    FIRSTNONBLANK ( DateTable[Start Date], 1 ) <= MAX ( 'Calendar'[Date] ) &&
    FIRSTNONBLANK( DateTable[End Date], 1 ) >= MIN ( 'Calendar'[Date] ),
    "Include",
    "Exclude"
)

and , add above Measure as a filter on your visualisation, where Date Included is Include

Than you can filter your Calendar table to single value, or range.

Also,only overlapping rows from your fact table will be displayed.

0
votes

The problem can be solved by using a separate calendar-table and a measure: PowerBI filter- selected Date between Start and End date

Another way to solve this, and keep using the two slicers more intuitively, is described here: https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields. The solution describes how to set the properties of those timeline-slicers so that it makes a bit more sense for the user (set the "Start of employment" slicer as Type - After and the "End of employment" as Type - Before).