Dax performance of calculatetable vs filter with multiple conditions

[TrevLc]

Hi,

I thought calculatetable would provide better performace over the use of filter to create a filtered table (in a variable) when using multiple filter conditions.

However, I am seeing the opposite (and it is very significant)

Can someone please help me understand why calculatetable is significantly slower? 

Here is my measure:

I have commented out the alternative (that is faster) for "var b"

Attribute List = 

var a = VALUES(‘Table’[ID]) //This list is obtained when a user clicks on another visual aggregated by another column

var b =  CALCULATETABLE(

           SUMMARIZE(‘Table’, ‘Table’[ID], ‘Table’[Attribute]),

                ‘Table’[ID] in a

                ,’Table’[Attribute] <> "Count"

                )

// var b = FILTER(SUMMARIZECOLUMNS( ‘Table’[ID], ‘Table’[Attribute],

//                 FILTER(‘Table’, ‘Table’[ID] in a && ‘Table’[Attribute] <> "Count")

//             ), TRUE()

//         )

var c = CONCATENATEX(b,’Table’[Attribute]," , ")

RETURN

IF(ISINSCOPE(‘Table’[ID]),c)

To keep it simple, here is a small sample of my data: 

(my actual data is not that big (about 500k rows), but has more columns than below - I have only given relavant columns)

ID	Attribute
A	Count
A	Excel
A	Word
A	Access
A	PowerPoint
B	Count
B	Access
B	PowerPoint
C	Count
C	Word
D	Count
D	Excel
D	Word
D	PowerPoint

[Anonymous · Accepted solution]

Hi @TrevLc 

In fact, you're right, in general, CALCULATETABLE() runs faster than FILTER(), but there are exceptions:

When you need more than one filter depending on external contexts and you have to evaluate a distinct count(The SUMMARIZE() function) measure.

Here is a link for your reference:

Filter vs Calculatetable

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TrevLc]

Thank you, but it doesn't explain the performance difference, because I am comparing Filter Vs Calculatetable, which both result in a table.

I should also add, when I remove the additional condition of  ,’Table’[Attribute] <> "Count" in my calculatetable function, it performs ok.

[Irwan]

hello @TrevLc 

here is a good article about calculatetable vs calculate.

CALCULATE & CALCULATETABLE - What's The Real Diffe... - Microsoft Fabric Community

it might have performance impact since CALCULATE results in scalar and CALCULATETABLE results in table (or virtual table). perhaps that is what you are looking for.

Hope this will help.
Thank you.