Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi folks,
I'm trying to understand the performance difference between the IN operator when the argument is a single column calculated table and the IN operator when the argument is a list of values. The content of the two is identical but when the measure is used in a drill-through, the performance of the single column calculated table is absolutely abismal.
Measure with Calculated Table v1:
var single_column_table = CALCULATETABLE(DISTINCT('t1'[ID])
, FILTER(................)
FILTER(..................)
)
RETURN
CALCULATE(COUNTROWS('t2'[Other ID])
, FILTER('t2', 't2'[ID] IN single_column_table)
)
Measure with Calculated Table v2:
var single_column_table = CALCULATETABLE(DISTINCT('t1'[ID])
, FILTER(................)
FILTER(..................)
)
RETURN
CALCULATE(COUNTROWS('t2'[Other ID])
, single_column_table
)
Measure with List:
CALCULATE(COUNTROWS('t2'[Other ID])
, FILTER('t2', 't2'[ID] IN {list})
)
* list contains all values in single_column_table
* t1 1 ---> * t2
In my drill through target I have fields from t1 and fields from t2
The performance of the first two approaches is very poor while the performance of the third approach is very good.
Appreciate any thoughts, this is driving me crazy
TDM
Solved! Go to Solution.
@Anonymous
IN should followed by a table expression, and the curly braces { } is also a table constructor. So the calculate table and { } both create a virtual table in your case.
IN {} performed better is because your calculate table contains filters that calculation must run through the whole table, which is more complex than { }, where you just need to enter the results directly.
Hope it is clear.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
IN should followed by a table expression, and the curly braces { } is also a table constructor. So the calculate table and { } both create a virtual table in your case.
IN {} performed better is because your calculate table contains filters that calculation must run through the whole table, which is more complex than { }, where you just need to enter the results directly.
Hope it is clear.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |