Filter 2 dates column in 1 table

[joshrumbawa]

Hello PBI Experts,

I have a requirements to filter the fact table based from Date Dim table. I have 2 dates column to consider the Application Date and the Approved date.

In my datamodel, the date is connected to the Application date. However, they require me to include the record from Approved Date. 

For Ex. I filter the dates from Feb 1 - Feb 29. The result will show all the records that covers from that range based from Application Date and Approved Date.

I tried to search, and so far there's 2 option

1. Is to create a INACTIVE connection in a datamodel, then use the USERELATIONSHIP in DAX.
2. Is to create DAX that will create a flag if the date range condition met then put the flag on each chart.

I tried both, but it's not suitable to my requirements. 
Is there a workaround in a datamodel? or someone encounter same scenario?

In SQL, this can be done using WHERE Application Date OR Approved Date clause.

Please help I got stuck on this issue. Thank you in advance! 

[Anonymous · Accepted solution]

Hi @joshrumbawa 

First of all, thanks to @lbendlin and @Wilson_ for your timely reply.

Here is the test I did.

My sample:

There is no relationship between the two tables.

Create a measure:

Measure = IF(MAX([Application Date]) >= MIN('Date'[Date]) && MAX([Application Date]) <= MAX('Date'[Date]) || MAX([Approved Date ]) >= MIN('Date'[Date]) && MAX([Approved Date ]) <= MAX('Date'[Date]), 1, 0)

Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wilson_]

Hi joshrumbawa,

I'm curious why you say it's not sustainable for your requirements. Can you elaborate? It seems the second option you listed should be the most straightforward approach. As @lbendlin says, you cannot have multiple active relationships at the same time.

[lbendlin]

With the standard data model you have two options

- AND - filter on both columns, filters will overlay

- EITHER OR - employing USERELATIONSHIP you can switch the filter between the columns

What you want is OR - that is not supported in the default data model.  It requires disconnected tables to feed your slicers/filters, and measures as visual filters to apply the selections to the data model's fact table.