How to use DAX to filter rows in table based on a date slicer

[MrHajiCoolBreez]

I'm new to PBI and DAX.

I've inherited a PBI report that uses the MS timeline slicer custom visual.

The problem I'm trying to resolve is to only show data in a table where any period inside the date/date range selected falls within the StartDate and EndDate of a table of data.

Based on the following subset table of data...

- If February 2024 was selected in the Timeline Slicer then only rows with Id 1,2 would be shown.

- If July 2024 was selected in the Timeline Slicer then only rows with Id 2 would be shown.

- If September 2024 to January 2025 was selected in the Timeline Slicer then only rows with Id 3,4 would be shown.

- If January 2025 was selected in the Timeline Slicer then no rows would be shown.

- If Q1 2024 was selected in the Timeline Slicer then only rows with Id 1,2 would be shown.

- If Q3 2024 was selected in the Timeline Slicer then only rows with Id 4 would be shown.

Including a 1-2-many relationship between the `Calendar` and `Data` table isn't going to work here.

I'm thinking the way to do would be to use measures.

One that will hold the value of the `MIN` date selected, and another to hold the `MAX` date selected.

`SelectedMinDate = DATEVALUE(MIN('Calendar'[Date]))`

`SelectedMaxDate = DATEVALUE(MAX('Calendar'[Date]))`

But how I would use these to filter the result set in the table is where I get stuck.  These might  not be the correct measures in the first place.
What do I need further?

This is the DDL for the sample data set

DROP TABLE IF EXISTS dbo.Test1

CREATE TABLE dbo.Test1
(
Id INT IDENTITY(1,1) ,
Activity VARCHAR(255) ,
StartDate DATE ,
EndDate DATE
);

INSERT INTO dbo.Test1
(Activity,StartDate,EndDate)
SELECT
T.Activity,
T.StartDate ,
T.EndDate
FROM (VALUES('Test1', '20240101', '20240430'),
('Test2', '20240301', '20240630'),
('Test3', '20240501', '20241031'),
('Test4', '20241101', '20241231')
) T(Activity,StartDate,EndDate)

[lbendlin]

I'm thinking the way to do would be to use measures.

yes, that is one part of the solution.  The other part is that the timeline slicer must be fed from a disconnected table.  Then use your measure to sense what is selected there, and to compute the visibility of the rows in your data model.  Lastly use that measure as a visual level filter.

[MrHajiCoolBreez]

Thanks, I appreciate the response but like I said, I'm new to PBI and DAX, so how would I do that exactly.

The timeline slicer is currently fed by a calendar table that is different to the table of data.  There's a 1-2-many relationship between the two tables.

How do I implement what you've suggested.

[lbendlin]

Cut the relationship, or add a disconnected clone of the calendar table.

[MrHajiCoolBreez]

I understand that but how do I implent the second part of this?

"Then use your measure to sense what is selected there, and to compute the visibility of the rows in your data model."

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MrHajiCoolBreez]

I've provided DDL at the bottom of my question and the expected results in the bullet points below the screenshot in the question.

[lbendlin · Accepted solution]