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
thmonte
Helper IV
Helper IV

Filtering within a date range

I have multiple tables with a Start Date column and and End Date Column and I would like to filter based on the Date Slicer falling within the range.  For example:

 

TypeStart DateEnd Date
Sick2/1/20173/1/2017

 

I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.

 

How can I achieve this?

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @thmonte,



I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.

If I understand you correctly, you should be able to follow steps below to use a Date Slicer to filter your data on your visual. Smiley Happy

 

1. Add an individual Calendar table if you don't have one yet(make sure not create any relationship between this table and your original table).

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and use it to apply a visual level filter(Count of Type is greater than 0) on your visual.

Count of Type =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
        )
    )

Note: just replace 'Table1' with your real table name.

 

Regards

View solution in original post

6 REPLIES 6
v-ljerr-msft
Employee
Employee

Hi @thmonte,



I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.

If I understand you correctly, you should be able to follow steps below to use a Date Slicer to filter your data on your visual. Smiley Happy

 

1. Add an individual Calendar table if you don't have one yet(make sure not create any relationship between this table and your original table).

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and use it to apply a visual level filter(Count of Type is greater than 0) on your visual.

Count of Type =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[Start Date] <= currentDate
                && Table1[End Date] >= currentDate )
        )
    )

Note: just replace 'Table1' with your real table name.

 

Regards

@v-ljerr-msft 
How to do the relatationship between 'Date' and 'Table1' when on right side you have [Start date] and [End date]?
Between which fields it should be done?


The measure [Count of Type] returs the count of rows in a selection. How it can filter visual ?

Is this impossible to do if I am using DirectQuery?  I want to have my data refresh in real time once published.

Hi @thmonte,

 

Yes, it is. As we cannot add calculate tables in DirectQuery mode, you may need to add the Calender table on your source side instead. And the other steps are the same. Smiley Happy

 

Regards

blopez11
Resident Rockstar
Resident Rockstar

I think you would need to create a date table, relate it to your other tables based on date

Then create a filter based on date from your date table, and a filter on whatever table has the type

 

@blopez11

 

How does that help me with filtering values within a date range?

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.