topic Re: How to use DAX to filter rows in table based on a date slicer in DAX Commands and Tips

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

MrHajiCoolBreez — Wed, 12 Jun 2024 11:32:28 GMT

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)

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

lbendlin — Thu, 13 Jun 2024 00:50:41 GMT

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.

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

MrHajiCoolBreez — Thu, 13 Jun 2024 07:53:34 GMT

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.

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

lbendlin — Thu, 13 Jun 2024 13:34:06 GMT

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

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

MrHajiCoolBreez — Thu, 13 Jun 2024 13:42:08 GMT

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."

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

lbendlin — Thu, 13 Jun 2024 13:47:12 GMT

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-Forum/ba-p/963216

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/1447523

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

MrHajiCoolBreez — Thu, 13 Jun 2024 13:50:31 GMT

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

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

lbendlin — Thu, 13 Jun 2024 14:28:16 GMT