Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mork
Helper V
Helper V

How to double filter using two date?

I have a table that has the bellow fields:

 

Project Name

Start Date

Completion Date

 

I want to have a visual table that has those three fields, this is quite easy to do.

 

But I also want to filter that table by both the Start Date and the Completion Date. I want to be able to select a date range and the table will show me all the projects that either the Start or the Completion Date are within the date range I selected.

 

I tried creating a datetable and relating both the Start Date and the Completion Date with that datetable but powerbi allows only one of the relationships to be active.

 

How can I achieve what I want?

2 REPLIES 2
Framet
Resolver II
Resolver II

Hi, 

 

You could create the create the date table but without a relationship at all. 

 

That way you could adjust the filter context of the measure accordingly.

 

For example to get a count of all projects of which at least one day crosses your date range filtered on the data table:

ProjectsCrossingDates :=
CALCULATE (
    COUNTROWS ( 'ProjectTable' ),
    FILTER (
        'ProjectTable',
        [Start Date] <= MAX ( DateTable[Date] )
            && [Completion Date] >= MIN ( DateTable[Date] )
    )
)

Note that having no active relationship to date requires that all measures requring time intelligence need to manually filtered in the way in which you expect them to react to the users date selections.

Hope this helps.

 

Thomas

Baskar
Resident Rockstar
Resident Rockstar

1. For this case u have to duplicate the table and create two relationship one for start Date and another one for completion date.

 

2. one more way is using Userelationship  function in calculations . 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.