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
Anonymous
Not applicable

Filter records between two date columns with one slicer

Hi,

 

I have a Tasks table that has the columns StartDate and EndDate, i created a date dimension table for the StartDate, StartDateDim that has a column Year that is filtered in the Dashboard in a slicer, for this year ('2019') is working fine since every tasks for this year have been created this year, but for the next Year this will not work because the calculations will not get the tasks that started in '2019' and that will end in '2020'. 

I did some search and found this topic that seems to have the solution:

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...

So i created the following measure (didn't use SELECTVALUE because i have tabular 1200):

DateFilter =
  IF (
      FIRSTDATE( Tasks[StartDate] ) <= MAX ( 'StartDateDim '[Date] )
           && LASTDATE( Tasks[EndDate] ) >= MIN ( 'StartDateDim '[Date] ),
      1
  )

But Power BI doesn't let me put the measure in the slicer Visual level filters.

Am i doing something wrong? Or is it because of the version of the tabular that i'm using? Or because i'm using Power BI Optimized for Report Server?

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Slicer visual doesn't support measure.

You need to add measures in the visual filter.

3-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Thank you @v-eachen-msft , that solved the problem but i still have one problem, the measure:

 

DateFilter =
  IF (
      FIRSTDATE( Tasks[StartDate] ) <= MAX ( 'StartDateDim '[Date] )
           && LASTDATE( Tasks[EndDate] ) >= MIN ( 'StartDateDim '[Date] ),
      1
  )

is not working, in this topic

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...

they recommend the following metric:

Measure =
IF (
    SELECTEDVALUE ( Table1[Start date] ) <= MAX ( 'Calendar'[Date] )
        && SELECTEDVALUE ( Table1[End date] ) >= MIN ( 'Calendar'[Date] ),
    1
)

but i still  don't have the SELECTEDVALUE in my tabular version, do you know if i can use the metric without the SELECTEDVALUE function? 

AmanBedwal
Frequent Visitor

@Anonymous you need to connect both StartDate and EndDate with DateTable you have in your model. By doing so you would be having one active and one inactive relationship lets say StartDate and Date Table is marked as active relation and EndDate is marked as inactive. The idea is when you use slicer by using USERELATIONSHIP you can dynamically change the relationship to active and get EndDate filtered.

Refer to this tutorial on how to do use a single slicer to filter multiple date columns.

 

Did I answer your question? Mark my post as a solution.

 

Regards

Aman Bedwal

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.