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
ThaddeusB
Helper I
Helper I

Filter for active within time range

I have a set of data like (plus additional columns:

 

ClientEntry DateExit Date
2053/24/20183/26/2018
20612/9/20097/26/2017
20711/10/201712/21/2017
20810/29/201711/1/2017
2084/10/20185/11/2018
2085/20/20187/17/2018
2094/25/20177/18/2018
2104/17/20184/18/2018
21110/25/201710/26/2017

 

I would like to filter the data for people active in a selectable time frame.  For example, selecting the range 10/1/17-12/1/18 should include 207,208, 209, & 211.  Formally, the rule would be [Entry Date]<=filter end AND [Exit Date]>= filter start.

 

I set up a Calendar table containing the days in the date range and made a slicer with that.  I am struggling with setting up the measure to filter my visualizations.  Any suggestions?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@ThaddeusB

 

try something on these lines

See file attached as well

 

Then you can use the MEASURE as a visual filter

 

Measure =
COUNTROWS (
    FILTER (
        table1,
        Table1[Entry Date] >= MIN ( 'Calendar'[Date] )
            && Table1[Exit Date] <= MAX ( 'Calendar'[Date] )
    )
)

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

The result should also include 205 and 210.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you both.  I had set up the filter very similarly, but was not getting the expected results.  My problem was that I was putting the measure ONLY in the visual filters of a pie chart and using count(client) for the values.  Putting count(measure) in the values section instead of count(client) fixed it.

Zubair_Muhammad
Community Champion
Community Champion

@ThaddeusB

 

try something on these lines

See file attached as well

 

Then you can use the MEASURE as a visual filter

 

Measure =
COUNTROWS (
    FILTER (
        table1,
        Table1[Entry Date] >= MIN ( 'Calendar'[Date] )
            && Table1[Exit Date] <= MAX ( 'Calendar'[Date] )
    )
)

 

 

 


Regards
Zubair

Please try my custom visuals

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.