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
arunaramana
Helper III
Helper III

table filter expression in CALCULATE() function

Hello There,

As I have gone through microsoft DAX  -- CALCULATE() function documentation, I came across 'Boolean Filter Expression', 'Table Filter Expressions', 'Filter Modification Functions'.

 

I didn't understand about 'Table Filter Expression' in comparison with 'Boolean Filter Expression'. Could anyone please help me understand.

 

Thank youhttps://docs.microsoft.com/en-us/dax/calculate-function-dax 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User
v-eqin-msft
Community Support
Community Support

Hi @arunaramana ,

 

=CALCULATE (
    <expression>,
    table[column] = <value>
)

In reality the filter expression you wrote is transformed in:

CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value>
    )
)

 

The FILTER function in DAX is a simple function to use for filtering rows of a table. This function does not change the columns (unless it is used as an input of column manipulation functions such as SELECTCOLUMNS or ADDCOLUMNS). The filter function requires a table input and an expression. The expression should return true or false, and can include AND/OR functions or operators. Similar to many other tabular functions, the main benefit of this function is when used to create a virtual table in a measure expression.

 

Using a table filter, you inherit the filter argument existing for the  table, so you will not include a value if it was not present in the existing filter. What is more important, you will not override the existing filter on such a column.

 

https://radacad.com/filter-function-in-dax-and-power-bi-apply-custom-filter-to-calculations

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

 

Best Regards,
Eyelyn Qin
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

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

Hi @arunaramana ,

 

=CALCULATE (
    <expression>,
    table[column] = <value>
)

In reality the filter expression you wrote is transformed in:

CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value>
    )
)

 

The FILTER function in DAX is a simple function to use for filtering rows of a table. This function does not change the columns (unless it is used as an input of column manipulation functions such as SELECTCOLUMNS or ADDCOLUMNS). The filter function requires a table input and an expression. The expression should return true or false, and can include AND/OR functions or operators. Similar to many other tabular functions, the main benefit of this function is when used to create a virtual table in a measure expression.

 

Using a table filter, you inherit the filter argument existing for the  table, so you will not include a value if it was not present in the existing filter. What is more important, you will not override the existing filter on such a column.

 

https://radacad.com/filter-function-in-dax-and-power-bi-apply-custom-filter-to-calculations

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

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.