Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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!
@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 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.
Thank you so much Antriksh!
Hi @Anonymous
both measures are the same in terms of content but the first results in an error (see figur).
The second measure is ok!
Regards FrankAT
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
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!
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
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 |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |