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.





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 named TransactionHistory which contains product details as given below.

Lets see the behaviour of CALCULATETABLE function.

Basic use of CALCULATETABLE function

Lets create a measure named calctable to filter a table records for quantity >1 using following DAX.

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

Once you commit DAX function, a table named as calctable is created in data model.

This table is just a copy of actual table TransactionHistory but contains only those products for which a quantity value is greater than1.

Lets drag a table fields into a table visual, and you can see all the products having a quantity greater than 1.

You can also compare both tables result sets, you can clearly see that calctable contains only those products for which quantity value is greater than 1.

So, using CALCULATETABLE function you can sort table records based on any specific condition.

CALULATETABLE function with Summarize function

As you have seen above, a CALCULATETABLE function returns a table based on given filter conditions, It returns all columns of base table, but in case when you want to see only specific columns then this can be done using SUMMARIZE function within CALCULATETABLE function.



Lets modify the above DAX calculation and returns only specific columns ProductID, Quantity, and TransactionDate only.

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

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

Using CALULATETABLE function within measures

You can also use CALCULATETABLE function within measure based on requirement so rather than creating a separate filtered table it can be used inside any measure.

SumofQuantity>1 =

SUMX(
CALCULATETABLE (
TransactionHistory,
TransactionHistory[Quantity] > 1
),TransactionHistory[Quantity])

Now once you commit the DAX, and drag the measure into card visual.

As you can see, it returns a total quantity sum of products for which quantity is greater than1 that is 21 as shown in below screenshot.

Also Read..

DAX – ALL




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,708 total views,  21 views today

Leave a Reply

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