cancel
Showing results for 
Search instead for 
Did you mean: 
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

@arunaramana , Check are you looking for this?

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

 

Or an example would be better 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Eyelyn9
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
Eyelyn9
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

amitchandak
Super User
Super User

@arunaramana , Check are you looking for this?

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

 

Or an example would be better 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.