Forum Discussion

haichenhuang's avatar
haichenhuang
Frequent Visitor
2 years ago

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?

Need Help
Tips and Tricks

5 Replies

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

    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])

     

     

     

    • haichenhuang's avatar
      haichenhuang
      Frequent Visitor
      2 years ago

      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])

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

    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. 

     

    • haichenhuang's avatar
      haichenhuang
      Frequent Visitor
      2 years ago

      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.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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