Power BI - Select Slicer Date Between 2 Columns

Question

Hopefully a quick explanation of what I am hoping to accomplish followed by the approach we've been working on for over a year.

Desired Result I have a table of SCD values with two columns, SCD_Valid_From and SCD_Valid_To. Is there a way to join a date table in my model (or simply use a slicer without a join) in order to be able to choose a specific date that is in between the two SCD columns and have that row of data returned?

Original Table

ID   |  SCD_Valid_From  |  SCR_Valid_To  |  Cost
1       2020-08-01         2020-08-03       5.00

Slicer date chosen is 2020-08-02. I would like this ID=1 record to be returned.

What We've Attempted So Far We had a consultant come in and help us get Power BI launched last year. His solution was to create an expansion table that would contain a row for every ID/Date combination.

Expanded Original Table

ID   |  SCD_Valid_Date  |  Cost
1       2020-08-01         5.00
1       2020-08-02         5.00
1       2020-08-03         5.00

This was happening originally on the Power BI side, and we would use incremental refresh to control how much of this table was getting pushed each day. Long story short, this was extremely inefficient and made the refresh too slow to be effective - for 5 years' worth of data, we would need over 2000 rows per ID just to be able to select a dimensional record.

Is there a way to use a slicer where Power BI can select the records where that selected date falls between dates in two columns of a table?

As per my knowledge, this is the only option to mage your requirement. How you are creating that Expanded table? Using CROSSJOIN is an option. — mkRabbani
Question: Do you have single day selection in your slicer? if yes, there might have a workaround. — mkRabbani
@mkRabbani, Yes, the slicer has individual dates. So in my example, the user would want to select 2020-08-02 and want to see the ID=1 record returned. — rlphilli
Using the a CROSS JOIN on the SQL side isn't an issue and is how we've done it. Rather it's the fact that a rather small table blows up to over 3 billion rows, with 2-3 million added per day. Given the size of our data warehouse, would constitute ~50% of the size of our warehouse as it is now. — rlphilli
Yes, they will only select 1 day at a time. Let me try your solution below and I will get back to you. Thank you. — rlphilli

mkRabbani mkRabbani · Accepted Answer · 2020-08-04T13:34:52

Let me explain a workaround and I hope this will help you to solve your issue. Let me guess you have below 2 tables-

"Dates" table with column "Date" from where you are generating the date slicer.
"your_main_table" with with column "scd_valid_from" and "scd_valid_to".

Step-1: If you do not have relation between table "Dates" and "your_main_table", this is fine as other wise you have to create a new table like "Dates2". For this work around, you can not have relation between those tables.

In case you have already relation established between those tables, create a new custom table with this below code-

Dates2 = 
SELECTCOLUMNS(
    Dates,
    "Date", Dates[Date]
)

From here, I will consider "Dates2" as source of your Date slicer. But if you have "Date" table with no relation with table "your_main_table", just consider "Dates" in place of "Dates2" in below measures creation. Now, Create these following 4 measures in your table "your_main_table"

1.

date_from_current_row = max(join_using_date_range[SCD_Valid_From])

2.

date_to_current_row = max(join_using_date_range[SCD_Valid_to])

3.

date_selected_in_slicer = SELECTEDVALUE(Dates2[Date])

4.

show_hide_row = 
if(
    [date_selected_in_slicer] >= [date_from_current_row] 
        && [date_selected_in_slicer] <= [date_to_current_row] 
    , 
    1, 
    0
)

Now you have all instruments ready for play. Create your visual using columns from the table "your_main_table"

Final Step: Now just add a visual level filter with the measure "show_hide_row" and set value will show only when "show_hide_row = 1".

The final output will be something like below image-

Power BI - Select Slicer Date Between 2 Columns

1 Answers