Filter between effective dates

[davidcray75]

Hello,

Should I expect slow query response times when filtering an imported table with a dynamically selected date where it falls between row effective start and end dates? The link below contains a simple example with a year-over-year variance against 660K rows. It takes 8 seconds to trend 365 days. I expect the response time to be immediate with several times as many records. 

Example of DAX I'm trying to speed up:

Active (#) =

VAR _maxDate = MAX('Date'[Calendar_Date])

RETURN

SUMMARIZE (

    FILTER('Active',

            'Active'[ROW_EFFECTIVE_START] <= _maxDate

                && 'Active'[ROW_EFFECTIVE_END] > _maxDate

    )

    ,"Total", SUM('Active'[ACTIVE_ITEMS_COUNT])

)

Files for example

In reality, I need to select between effective dates in tables between 13M and 20M rows, slice them by 10 different attributes, include one or two more date filters, etc.

I've also tried:

• Every combination of dax that filters, counts or sum I can think of
• Using table variables to pull out pieces to sum and/or intersect back together later
• A normal dimension linked by a primary key to fact tables
• Adding inactive relationships to the effectives dates with no timestamp (which would be an incorrect result without timestamp)

I am always required to either; (1) expand the context of the customer and date records I'm directly linked to in the relationships, or (2) work with disconnected tables. In either case, opening up context kills the query time.

When I profile the script there are huge combinations of effective dates passed into an IN clause in the xmSQL, but I don't know if that's something the vertipaq engine should be able to handle well. I'm concious of the preference to keep things in the storage engine, but the filter always increases the time used by the formula engine considerably. It does appear to reuse most of the SE cache.

Any help is much appreciated! I'm not sure how to get this reassigned to the proper DAX category. 

[lbendlin]

The Italians have some great videos on how to refactor queries to reduce cardinality. Apply filters first that have the biggest impact, then apply the other filters sequentially.  

Instead of FILTER()  try using CALCULATETABLE() .  Use DAX Studio to watch for the number of records in each query plan.

[davidcray75]

Thanks for the reply. I've been enjoying their videos already...feels like I've tried it all!