topic Re: Dax performance of calculatetable vs filter with multiple conditions in DAX Commands and Tips

Dax performance of calculatetable vs filter with multiple conditions

TrevLc — Tue, 27 Aug 2024 01:46:38 GMT

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

Re: Dax performance of calculatetable vs filter with multiple conditions

Irwan — Tue, 27 Aug 2024 02:12:08 GMT

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.

Re: Dax performance of calculatetable vs filter with multiple conditions

TrevLc — Tue, 27 Aug 2024 04:09:00 GMT

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.

Re: Dax performance of calculatetable vs filter with multiple conditions

Anonymous — Wed, 28 Aug 2024 06:54:16 GMT

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.