FILTER Performance in Composite Model with Aggs Very Slow

[Anonymous]

Hello... I have a composite model, with 2 levels of aggregate tables.  I have a measure that looks simple enough, but it is taking about 10 seconds to return results.  Here's the relevant segment of the model I'm working with. It's a pretty standard "Salesy" type of model...

Fact Tables:

1) Sales Agg Date Store - Import Mode - 4 million rows - Aggregate table

2) Sales Agg Extended - Import Mode - 20 million rows - Aggregate table

3) Sales Ticket - Direct Query Mode - 600 million rows - transaction level detail

This DAX measure is taking about 10 seconds to return when I add the FILTER clause:

        CALCULATE (
            SUM ( 'Sales Ticket'[Ticket_Count] ),
            FILTER (
                'Sales Agg Date Store',
                'Sales Agg Date Store'[Sales Above Threshold Flag]
            )
        )

The basic idea is I want to get a sum of Ticket_Count for days and stores where the sales threshold was met. (we don't count days with little activity). That sales above threshold flag is only in the highest level aggregate table.

I see in DAX Studio that the measure is mapping the aggregation successfully from the [Sales Ticket] table up to the [Sales Agg Date Store] table. So it should be pretty fast. But it isnt.  

I tried replacing the FILTER with CALCULATETABLE. No luck. Can anybody think of anything I am missing to help with the performance of this measure?  Maybe my model design needs to be blown up.  That could be too.

Thanks for looking!

[Anonymous]

There's a golden rule in DAX programming: NEVER USE A TABLE WHEN YOU CAN USE A COLUMN. You are violating this rule. You should never filter by full tables since this will ALWAYS be slow and, in fact, dangerous. Secondly, you should not use the aggregated tables in your formulas. Just don't create dependency on something that might not be there at all. Aggregate tables should be invisible to you. It's only a machinery to speed up queries/measures and it's intended solely for the SSAS engine. Please therefore change the measure and make sure that when you think aggregations are used, they really are.

[wdx223_Daniel]

how about using TREATAS to create a virtual relationship?

CALCULATE (
            SUM ( 'Sales Ticket'[Ticket_Count] ),
            TREATAS(CALCULATETABLE(VALUES('Sales Agg Date Store'[STORE_KEY]),
                'Sales Agg Date Store'[Sales Above Threshold Flag]
            ),'Sales Ticket'[STORE_KEY])

        )

[AlB]

Hi @Anonymous 

I see no relationship between  'Sales Agg Date Store' and 'Sales Ticket'. Am I seeing it incorrectly? If there is no relationship, what is the point of the filter?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB .. thanks for your response!

The idea is to pull the [Sales Above Threshold Flag] from the [Sales Agg Date Store] table and apply it to the aggregate hierarchy (i.e. any one of the three fact tables) based on their mutual relationships with the [Calendar] and [Store] tables.  

The filter provides the unique combinations of [STORE_KEY] and [Ticket_Created_date] that I need to include for sales on days where the store's total met the minimum daily threshold.

As far as I know, relationships between fact tables is not a best practice, but maybe it would help here.  I tried it a while ago, but the cardinality of the relationships was so high that it became a burden to refresh the model frequently.

[AlB]

@Anonymous 

Try perhaps using only the relevant column instead of the whole table as base for the filter:

CALCULATE (
            SUM ( 'Sales Ticket'[Ticket_Count] ),
            FILTER (
                ALL('Sales Agg Date Store'[Sales Above Threshold Flag]),
                'Sales Agg Date Store'[Sales Above Threshold Flag]
            )
        )

or

CALCULATE (
            SUM ( 'Sales Ticket'[Ticket_Count] ),
            FILTER (
                DISTINCT('Sales Agg Date Store'[Sales Above Threshold Flag]),
                'Sales Agg Date Store'[Sales Above Threshold Flag]
            )
        )

which would be actually be the equivalent of what you had (since you were not using ALL on the whole table)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks again for taking a look. I really appreciate it!  The proposed DAX didn't work. I think I just am better off propagating the filter column throughout all levels of my aggregation. So I added it in the source for all 3 fact tables, now I can just write it real easy like this...

"Ticket_Count_Above_Threshold", CALCULATE ( [Ticket Count], 'Sales Ticket'[Sales Above Threshold Flag] )

 ... and the aggregation magic in Power BI rolls it up as much as possible.  Amazing stuff.