Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX IN calculated table vs IN list of values

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

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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.
  

View solution in original post

1 REPLY 1
V-pazhen-msft
Community Support
Community Support

@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.
  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.