Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 )
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 )
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
@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.
@tamerj1 Wow! That CALCULATE Challenge knowledge is coming in handy!! 🙂
Thank you very much for your support.
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |