HELP! Use FILTER with MAX/MIN referencing different tables in measure: Exceeded resources.

[MWinter225]

Hey all,

I have a dataset that is 2M+ rows and this measure is killing my performance. How do I not use FILTER() and MAX()/MIN() together in a measure that references different tables? Below I'll lay out the measure in question and the other measure it references.  What I have done is set up a date filtering system where the user will select something like "Year to date" as a value in 'Dates'[Preset Dates] and then I want it to show the count for the product IDs filtering by > the MIN month and < the MAX month in the 'Dates' table that I Have set up. All the other measures work great, but there are over two million product IDs and its killing the report to have that FILTER dax in there. If it was all in one table I could use a column calculation, but I am referencing different tables here and can't do a column calculation that I know of. Any thoughts?  Thanks in advance!

Original Measure=
if(HASONEVALUE('Dates'[Preset Dates]),
CALCULATE([Count of Products]
,FILTER('Category','Category'[Accounting Month] <= MAX(Dates[Max Month])

&& 'Category'[Accounting Month] >= MIN(Dates[Min Month])

))
,CALCULATE([Count of Products]))

 Count of Products =
CALCULATE(DISTINCTCOUNT('Category'[ID]),'Category'[Type]="Office Supply" || 'Category'[Type]="General Merchandise")

[OwenAuger]

Hi there @MWinter225

As you've suggested, I would say the FILTER('Category'...) is killing the measure.

Generally speaking you should avoid filtering a fact table within a measure, and instead filter the specific columns required, in this case Accounting Month.

Also, it can improve performance if you directly apply the filtered values rather than using <= or >= conditions, as these are converted to FILTER (ALL(...)).

I could help come up with a specific change to your measure, but just want to check a few things first.

1. It looks like the 'Dates' table is disconnected from 'Category' and you are using it to apply a virtual filter, is that correct? (i.e. no relationships between 'Dates' and 'Category').
2. Just checking you don't have a traditional date table at present with contiguous dates? Not strictly required but just wanted to check before coming up with suggested DAX.
3. What are the data types of 'Category'[Accounting Month] and 'Dates'[Min Month] and 'Dates'[Max Month]? Dates or something else? And what convention do they follow, for example if they are months represented by dates are they all first-of-the-month dates?

Regards,

Owen

[MWinter225]

1. Yes. Dates table is disconnected..... there might be a relationship between ‘Dates’[Date] and ‘Category’[Accounting Month] one to many where both are dates and representing the first of the month (07/01/18, 08/01/18, etc.), but I don’t think it’s actually being used in the report.
2. Correct
3.correct. They are all first of the month dates.

Let me know if you need anything else! Thanks!!

[OwenAuger · Accepted solution]

Thanks for that!

Here's an initial suggestion - interested to hear whether this improves performance. All I've done is change the 2nd argument of filter to a filter on 'Category'[Accounting Month] using DATESBETWEEN.

Original Measure Updated =
IF (
    HASONEVALUE ( 'Dates'[Preset Dates] ),
    CALCULATE (
        [Count of Products],
        DATESBETWEEN (
            'Category'[Accounting Month],
            MIN ( Dates[Min Month] ),
            MAX ( Dates[Max Month] )
        )
    ),
    [Count of Products]
)

1. To verify the above suggestion and see if we should change anything (e.g. make use of traditional Date table), it would be useful to see a sample of the 'Dates' and 'Category' tables along with the relationship view.
2. By the way, I'm assuming that a given value of 'Dates'[Preset Dates] corresponds to a single row with a single value of Min Month and Max Month?

Regards,

Owen

[MWinter225]

@OwenAuger  That did the trick! Thank you so much! You saved me a lot of headache!