Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
haichenhuang
Frequent Visitor

Date range filter slow performance

Hi there,
I want to filter based on date ranges according to this logic:

 

'Work'[Start Date] <= MAX('Calendar'[Date])

 && 'Work'[End Date] >= MIN('Calendar'[Date])

 

Originally using a date between splicer did not work, so I had to use a dax measure as a filter:

 

Filter Measure =

IF(

    NOT ISEMPTY(

        FILTER(

            'Work',

            'Work'[Start Date] <= MAX('Calendar'[Date])

                && 'Work'[End Date] >= MIN('Calendar'[Date])

        )

    ),

    1

)

 

However, the dax measure provides extremely slow performance, to the point where the visuals cannot be displayed. Though, the splicer performed faultlessly (except the fact it was not how I wanted to filter by). Note, I am working with 300k rows of data.

 

Is there any way I can achieve my desired filter with good performance?

5 REPLIES 5
v-cgao-msft
Community Support
Community Support

Hi @haichenhuang ,

Please try this measure:

Filter Measure 2 = 
VAR __min_date = MIN('Calendar'[Date])
VAR __max_date = MAX('Calendar'[Date])
VAR __cur_start_date = SELECTEDVALUE('Work'[Start Date])
VAR __cur_end_date = SELECTEDVALUE('Work'[End Date])
VAR __filter = IF(__cur_start_date>=__min_date && __cur_end_date<=__max_date,1)
RETURN
__filter

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

olgad
Super User
Super User

Ok, if that is your requirement, fine. 
What is it that you are trying to calculate? Could you please explain the logic of what you would like to calculate with that, then we can check if the calculation you want originally was actually logically correct and can find the solution to optimize it. 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Okay, so the objective is to display in a table the work items ( 'Work') that are relevant in a given date range. I have a Calendar table for the dates in a splicer. What I mean by relevant is if the the start or end dates of the work item occurs anytime in the given date range. From this I've thought the logic to be if the Work start date is less than the given end date, and the work end date is greater than the given start date:

'Work'[Start Date] <= MAX('Calendar'[Date])

&& 'Work'[End Date] >= MIN('Calendar'[Date])

 

So in the table, i've inserted Filter Measure (see orig. post) into the Filter section, and show if Filter mEasure = 1. Where the table has columns related to 'Work' table.

 

 

olgad
Super User
Super User

Hi, why the original slicer didnt work?

in you original confition, it has to be 

'Work'[Start Date] >=Min('Calendar'[Date])

 && 'Work'[End Date] <= Max('Calendar'[Date])

 

 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Because I don't want the slicer to perform as: 

'Work'[Start Date] >=Min('Calendar'[Date])

 && 'Work'[End Date] <= Max('Calendar'[Date])

 

Rather, I want it to do the following - note how it is different:

'Work'[Start Date] <= MAX('Calendar'[Date])

 && 'Work'[End Date] >= MIN('Calendar'[Date])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors