topic Re: Why is my slicer not affecting performances? in DAX Commands and Tips

Why is my slicer not affecting performances?

Uchenzei — Wed, 08 May 2024 09:57:08 GMT

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

Re: Why is my slicer not affecting performances?

tamerj1 — Wed, 08 May 2024 11:19:52 GMT

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 )

Re: Why is my slicer not affecting performances?

Uchenzei — Wed, 08 May 2024 11:40:57 GMT

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

Re: Why is my slicer not affecting performances?

tamerj1 — Wed, 08 May 2024 13:53:29 GMT

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.

Re: Why is my slicer not affecting performances?

Uchenzei — Wed, 08 May 2024 14:07:49 GMT

Thank you very much for your support.

Re: Why is my slicer not affecting performances?

Greg_Deckler — Wed, 08 May 2024 14:50:02 GMT

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

Re: Why is my slicer not affecting performances?

tamerj1 — Wed, 08 May 2024 15:39:45 GMT

Greg_Deckler

Exactly!