Home » CALCULATETABLE function DAX

CALCULATETABLE function DAX

CALCULATETABLE function is a power bi filter function in DAX that evaluates a table expression in a context modified by the given filters. It returns a table of values.





DAX SYNTAX

CALCULATETABLE(<expression>,<filter1>,<filter2>,…)

<expression> is a table expression to be evaluated. A measure can not be used as expression.

<filter1>,<filter2>.. is a boolean expression or a table expression that defines a filter.

Lets look at an example of  DAX using CALCULATETABLE  function.

Here we have sample table TransactionHistory which is having a product details as given below.

Basic use of CALCULATETABLE function.

Now we will use calculatetable function named as calctable to filter a table records based on quanity >1 using following DAX.

CalCtable = CALCULATETABLE(TransactionHistory,TransactionHistory[Quantity] >1)

Once you commit this DAX function, a table named as calctable is created in power bi desktop.

This table is just a copy of actual table TransactionHistory but having only those products for which a quantity value is >1.

When you darg a table fields into a table visual , you see a product details only for quantity >1.

You can also compare both tables result sets, you can clearly see that calulatetable having only those products for which quantity value is >1.

 

So, using calculatetable function you can sort table records using any condition.

CALULATETABLE function with specific columns

As you have seen above calculatetable function returns a table based on given filter conditions, It returns all columns of base table, but in case if you want to see only specific column?

That can be done using SUMMARIZE function within CALCULATETABLE function.



Lets modify the above DAX calculation and returns only ProductID, Qunatiy, and TransactionDate only.

CalCtable =

CALCULATETABLE (

SUMMARIZE (

TransactionHistory,

TransactionHistory[ProductId],

TransactionHistory[Quantity],

TransactionHistory[TransactionDate]

),

TransactionHistory[Quantity] > 1

)

 

As soon you commit DAX, you will get only those columns that you want to see as shown in below screenshot.

 

Using CALULATETABLE function within measures

We can also use CALCULATETABLE function within Measure based on requirement so rather than creating a separate filtered table it  can be used inside a measures.

SumofQuantity>1 =

SUMX(

CALCULATETABLE (

TransactionHistory,

TransactionHistory[Quantity] > 1

),TransactionHistory[Quantity])

 

 

 

 

 

 

 

Now once you commit the DAX, and darg the measure on card visual, it returns a total quantity sum of products for which quantity is >1 that is 21 as shown in below screenshot.

Recommended…

DAX – ALL




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.