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

How to check if table of date ranges overlap with dates selected on slicer

Hi everyone!

 

So, I'm trying to figure out if a table of Activities with corresponding date ranges (i.e. start and end dates) have any dates which overlap with a selection from a Dates slicer. I'm not sure how to relate the Dates table to the Activities. As an illustration, these are the Activities:

ActivityStart DateEnd Date
A01-Jan-201931-Dec-2019
B11-Oct-201824-Apr-2019
C05-Mar-201826-Feb-2020
D11-Aug-201905-Nov-2020
E17-May-201916-Sep-2019
F03-Mar-201805-Jun-2018
G02-Jan-202001-Jan-2021

 

If I were to use my slicer like so: 

Start1-Jan-19
End31-Dec-20


then I should get the following Activities:

Activity
A
B
C
D
E

This is because each of these Activities share occurrence dates with the slicer dates.

Edit:

Ultimately, I'm trying to create a measure, ActivitiesCount, that can count the rows of this resulting set of Activities. For this example, ActivitiesCount would equal five (5).

Can you help me please? Thanks in advance!

1 ACCEPTED SOLUTION

@jmeccles 

Using [ShowMeasure], defined previously:

 

ActivitiesCount =
SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

The Activity count should be 6 (not 5).  Activity G should also be included.  You may downloaded 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 @Ashish_Mathur! You're right that it's six (6) - I put the wrong end date (should've been 31-Dec-2019) so I'll make an edit.

Your solution works but I forsee there being an issue with the table generated - it's only seven (7) activities yet it produces a table of over 2,300 rows, so I don't think it would be feasible for a system of thousands of actvities.

Any other suggestions?

You are welcome.  No other suggestions.  Before you make any such assumption, please first try the solutio on your live dataset.


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

@jmeccles 

Using [ShowMeasure], defined previously:

 

ActivitiesCount =
SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )
Anonymous
Not applicable

Hi,

 

I implemented this solution as I was facing same issue and this worked.

Thanks for above solution.

 

However, I am facing another problem with this solution.

 

When we export underlying data from the visual, it gives all rows from the data table (not the filtered rows as per logic applied)

 

I tried below as solution to solve the export problem.
  Apply the "ActivitiesCount = 1" at visual level filter
  But the visuals show wrong results if I apply this filter

 

Could you please suggest how I can et right rows in visual export as well.

 

Regards,
Deeksha

Hi @Anonymous 

I don't quite follow. Could you perhaps share a .pbix in which you show what you are trying to do and what does not work?

Cheers

Anonymous
Not applicable

Hi @AlB ,

 

I have prepared one sample for this issue. (PFA the same)

Please take a look and suggest.

 

Sample File with issue.

 

 

Regards,

Deeksha

@AlB , thanks! The semicolons don't work for me but if I switch them for commas it appears to work fine.

AlB
Super User
Super User

Hi @jmeccles 

Try this:

1. Place Table1[Activity] in the rows of a visual table selecting 'Don't summarize'

2. Create this measure

 

ShowMeasure =
IF (
    MAX ( SlicerTable[SlicerColumn] ) < SELECTEDVALUE ( Table1[Start Date] )
        || MIN ( SlicerTable[SlicerColumn] ) > SELECTEDVALUE ( Table1[End Date] ),
    0,
    1
)

3. Place [ShowMeasure] in the visual level filters of the table visual and select  'Showw items when the value' is --> 1

 

 

Thanks, @AlB. That's pretty clever but what I'm really trying to achieve is a measure which can count all the Activities with dates occurring within the slicer date range. For the example I gave initially, my measure would return five (5) activities.

Is there a way that this can be achieved?

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.