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
Anonymous
Not applicable

DAX Operator

Hi, 

I have another question from Power BI practice test. The correct answer is said to be B,  I think the following two measures are exactly the same, or maybe "|| " can not be used in case A?

DAX Operator.PNG

 

 

2 ACCEPTED SOLUTIONS
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

They are both the same but one A will be more cost efficient.

 

See this video by Jeffery Wang - fro the team who created the DAX Engine

https://www.youtube.com/watch?v=bJtRB86n9tk

 

The difference is explained in 37:540 in the video.

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

View solution in original post

AntrikshSharma
Community Champion
Community Champion

@Anonymous Both measures are different, and the first one is incorrect here is why:

 

When you write a predicate/boolean logic in the filter argument of CALCULATE then internally it expands to a FILTER and ALL construct. Product[Color] = "Red" becomes 

= FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )

In the first option the two predicates refer to 2 different column of Products in the same filter argument of CALCULATE, i.e. Color and Size, since both of them will be expanded by the engine, but engine won't be able to create a combination of unique existing Color and Size from the predicates, that's why you are not allowed to use predicates that refer to more than 1 column in single filter argument of CALCULATE, Product[Color] = "Red" && Product[Color] = "Blue" is fine and won't throw an error

 

if you want to use 2 columns in the same CALCULATE filter argument you will have to use this:

=
CALCULATE (
    [Measure],
    FILTER (
        ALL ( Product[Color], Product[Size] ),
        Product[Color] = "Red" || Product[Size] = 50
    )
)

 

in the second option the naked reference to the Product table will respect any filter coming from anywhere in the report, but when you use predicates, ALL removes those filters. so in case if you wanted to have a 2 column filter while ensuring the new filter is intersected with the filter outside CALCULATE without overwriting it, then you need to use KEEPFILTERS.

 

=
CALCULATE (
    [Measure],
    KEEPFILTERS (
        FILTER (
            ALL ( Product[Color], Product[Size] ),
            Product[Color] = "Red" || Product[Size] = 50
        )
    )
)

 

Also don't use a table filter where column filter will do the job it is much more efficient as there is no need to apply every column of the products table to the filter context when it is not needed, also if you are unaware of expanded tables then the values returned by a measure could be surprising.

View solution in original post

6 REPLIES 6
AntrikshSharma
Community Champion
Community Champion

@Anonymous Both measures are different, and the first one is incorrect here is why:

 

When you write a predicate/boolean logic in the filter argument of CALCULATE then internally it expands to a FILTER and ALL construct. Product[Color] = "Red" becomes 

= FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )

In the first option the two predicates refer to 2 different column of Products in the same filter argument of CALCULATE, i.e. Color and Size, since both of them will be expanded by the engine, but engine won't be able to create a combination of unique existing Color and Size from the predicates, that's why you are not allowed to use predicates that refer to more than 1 column in single filter argument of CALCULATE, Product[Color] = "Red" && Product[Color] = "Blue" is fine and won't throw an error

 

if you want to use 2 columns in the same CALCULATE filter argument you will have to use this:

=
CALCULATE (
    [Measure],
    FILTER (
        ALL ( Product[Color], Product[Size] ),
        Product[Color] = "Red" || Product[Size] = 50
    )
)

 

in the second option the naked reference to the Product table will respect any filter coming from anywhere in the report, but when you use predicates, ALL removes those filters. so in case if you wanted to have a 2 column filter while ensuring the new filter is intersected with the filter outside CALCULATE without overwriting it, then you need to use KEEPFILTERS.

 

=
CALCULATE (
    [Measure],
    KEEPFILTERS (
        FILTER (
            ALL ( Product[Color], Product[Size] ),
            Product[Color] = "Red" || Product[Size] = 50
        )
    )
)

 

Also don't use a table filter where column filter will do the job it is much more efficient as there is no need to apply every column of the products table to the filter context when it is not needed, also if you are unaware of expanded tables then the values returned by a measure could be surprising.

Anonymous
Not applicable

Thank you so much Antriksh! 

FrankAT
Community Champion
Community Champion

Hi @Anonymous 

both measures are the same in terms of content but the first results in an error (see figur).

 

15-08-_2020_15-42-04.png

 

The second measure is ok!

Regards FrankAT

 

Sujit_Thakur
Solution Sage
Solution Sage

Dear @Anonymous ,

Both are same in reading 

but DAX A will have error 

And B will run Ok

 

 

A kudos is appreciated 

If this help accept as solution

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

They are both the same but one A will be more cost efficient.

 

See this video by Jeffery Wang - fro the team who created the DAX Engine

https://www.youtube.com/watch?v=bJtRB86n9tk

 

The difference is explained in 37:540 in the video.

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

pranit828
Community Champion
Community Champion

HI @Anonymous 

 

Yep, hey both rae same.

 

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

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.