Why is my slicer not affecting performances?

[Uchenzei]

Hi,

I have this table (say "myTable"):

type	date	invoice
red	1-1-2000	$100.00
red	1-2-2000	$120.00
red	1-3-2000	$140.50

and so on for 6,000 records, all "red" type.

This measure is returning how many years I need to wait (from that date on) to double my invoice:

waitfor =
VAR this_date = SELECTEDVALUE(myTable[date])
VAR target_invoice = SELECTEDVALUE(myTable[invoice]) * 2
VAR future_date = CALCULATE(MIN(myTable[date]), myTable[date]>this_date, myTable[invoice]>=target_invoice)
RETURN
DATEDIFF(this_date, future_date, YEAR)

I put both "date" and "waitfor" into a chart and it's all working well.

Then I add more records to myTable so that I have this:

6,000 records "red" type

6,000 records "yellow"

6,000 records "brown"

6,000 records "blue"

6,000 records "magenta"

6,000 records "cyan"

36,000 records total.

Then I add a slicer into my report page to slice for "type" column and in such slicer I select "red" only and as a result the previous measure stops working ("Visual has exceeded the available resources").

It looks like the measure is now taking 36,000 records into account instead of just 6,000. I expected the slicer selection to skips records other than "red". What's wrong in my knowledge, why is the first example working and the second isn't? And how can I get to have the second scenario working?

Thanks a lot

[tamerj1 · Accepted solution]

Hi @Uchenzei 
Please try

waitfor =
VAR this_date =
    SELECTEDVALUE ( myTable[date] )
VAR target_invoice =
    SELECTEDVALUE ( myTable[invoice] ) * 2
VAR future_date =
    MINX (
        FILTER (
            ALLSELECTED ( myTable ),
            myTable[invoice] >= target_invoice
                && myTable[date] > this_date
        ),
        myTable[date]
    )
RETURN
    DATEDIFF ( this_date, future_date, YEAR )

[Uchenzei]

Hi tamerj1,

your query works very well!

Can you please share your thoughts about your approach and why my query is not working like yours? Thank you

[tamerj1]

@Uchenzei 
That is a deficiency in the generated query plan when following CALCULATE approach. In many scenarios The NoCALCULATE approach "introduced by @Greg_Deckler " generates a much simpler query plan and performs faster. 
In your case the engine generates a query plan that includes creating, inside the formula engine, a table that is the result of the Cartesian product of the rows of the original table. In your case would be approximately 36,000 x 36,000/2 = 680,000,000 rows which exceeds the cash limitations. Filters are applied later on inside the formula engine.

[Greg_Deckler]

@tamerj1 Wow! That CALCULATE Challenge knowledge is coming in handy!! 🙂

[tamerj1]

@Greg_Deckler 

Exactly!

[Uchenzei]

Thank you very much for your support.