DAX IN calculated table vs IN list of values

[Anonymous]

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

[V-pazhen-msft · Accepted 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.