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 entire report based on a in between time?

I am attempting to create a report that all filters will react to and I am having some trouble.

 

My data set has two columns to pull off of -

 

start date/time & end date/time

 

I want to build something that I can use a date and time slicer to see if this record was "active" within this range

 

lets say I set my slicer to start date 1/9/2018 2:00PM to 1/10/2018 1:00PM, I would expect rows 1 & 2 to be the only two records in the report.  This goes for all visuals - so lets say I did a visual of distinct count of numbers in the column I would get 2.

 

I tried something very similar but it never works the way I intend it to.

 

I have a date table that has no relationship to my main data table.

 

I wrote a measure that gets a count of rows on the main table and returns a number which is fitlered based on the min/max from the data table (which has its own slicer).  I then put a filter on the visuals in the report for that mesaure and if greater than 0.  It kind of works but not totally how I expect.

 

#start dateend date
11/9/2018 10:00 AM1/10/2018 8:00 AM
21/10/2018 10:00 AM1/11/2018 8:00 AM
31/11/2018 10:00 AM1/12/2018 8:00 AM
41/12/2018 10:00 AM1/13/2018 8:00 AM
51/13/2018 10:00 AM1/14/2018 8:00 AM
61/14/2018 10:00 AM1/15/2018 8:00 AM
71/15/2018 10:00 AM1/16/2018 8:00 AM
81/16/2018 10:00 AM1/17/2018 8:00 AM
91/17/2018 10:00 AM1/18/2018 8:00 AM
6 REPLIES 6
Anonymous
Not applicable

Measure =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) )
)

 

THIS Should work

 

Capture 6.PNG

Will this work on all visuals?

Anonymous
Not applicable

Yes, This will work on all visuals.

parry2k
Super User
Super User

@thmonte  do you want slicer date range against start date / time or also end date /time?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Now that I am thinking about it..

 

Maybe the slicer should just be a single date/time

 

I would expect that if I select 1/1/2019 8:00AM

 

I would expect any records where the slicer date falls between the start date/end date to be filtered out on the report.

Hi @thmonte ,

 

Do you means filter records which in selected range? If this is a case, you can't direct use slicer to achieve this.

 

You need to add new slicer with date field who not has relationship to original tables. Then write a measure to compare current row contents(start and datetime) with selected date and return result tag, drag this measure to visual level filter to filter non matched records.

Tag Measure =
VAR _start =
    MAX ( Table[Start] )
VAR _end =
    MAX ( Table[End] )
RETURN
    IF (
        _start IN ALLSELECTED ( Date[Date] )
            || _end IN ALLSELECTED ( Date[Date] ),
        "N",
        "Y"
    )


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.