topic Re: DAX doing wrong with "not isblank over related fact" filter in DAX Commands and Tips

DAX doing wrong with "not isblank over related fact" filter

DmitryKo — Wed, 27 Sep 2023 09:00:54 GMT

In a simple example where we have a dimension D_Entities with each dimension having some numeric Attribute, and a F_Comments table where each dimension might or might not have rows corresponding to it (1-many relationship over Name) - the goal is to calculate minimum Attribute value over all dimemsions that have corresponding rows.

Why on earth a simple measure doesn't work right:

MinAttr = CALCULATE(

MIN(D_Entities[Attribute]),

NOT ISBLANK(F_Comments[Comment])

)

Expected result for MinAttr measure is 100 because "A" dimension is the only dimension that has corresponding rows in F_Comments, and it's Attribute value is 100:

It's all on the screen, really:

Re: DAX doing wrong with "not isblank over related fact" filter

nirali_arora — Wed, 27 Sep 2023 09:47:13 GMT

You can use the following measure

MinimumAttribute =

MINX (

FILTER (

ALL ( 'D_Entities' ),

COUNTROWS ( RELATEDTABLE ( 'F_Comments' ) ) > 0

[Attribute]

)

Re: DAX doing wrong with "not isblank over related fact" filter

OwenAuger — Wed, 27 Sep 2023 09:57:19 GMT

Hi DmitryKo

In order to retrieve rows of D_Entities for which F_Comments is nonempty, you can use the SUMMARIZE function.

Here are some variations on how you could do this:

MinAttr = CALCULATE ( MIN ( D_Entities[Attribute] ), SUMMARIZE ( F_Comments, D_Entities[Name] ) -- Could also use SUMMARIZE ( F_Comments, D_Entities[Number] ) )

MinAttr = -- SUMMARIZE F_Comments by D_Entities[Attribte] -- then return the minimum MINX ( SUMMARIZE ( F_Comments, D_Entities[Attribute] ), D_Entities[Attribute] )

MinAttr = CALCULATE ( MIN ( D_Entities[Attribute] ), F_Comments -- Apply the expanded F_Comments table as a filter )

The reason for the original unintended result was that a filter on the F_Comments table does not itself filter the D_Entities table, due to the relationship direction.

Suggest reading some articles on expanded tables.

e.g. http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

or SQLBI will have some.

Regards

Re: DAX doing wrong with "not isblank over related fact" filter

DmitryKo — Wed, 27 Sep 2023 11:42:16 GMT

Guys, thanks for the quick response, and we have at least several solutions that work:

Use the original measure (with ISBLANK), but change D_Entities to F_Comments cross-filter direction to Both;
Use the measure suggested by nirali_arora, except for the ALL() part that would remove much-needed context;
use SUMMARIZE() approach with essentially intermediate table (that I intentionally omitted due to Occam's razor)

Aesthetically, I'd take the first option - as it provides clearest code with minimal performance overhead, F_Comments being a relatively small "leaf" table. But maybe I'm wrong about performance here and one of the other options scales better? How to evaluate this?

Re: DAX doing wrong with "not isblank over related fact" filter

OwenAuger — Thu, 28 Sep 2023 07:05:42 GMT

DmitryKo you can use the Server Timings or Query Plan features in DAX Studio.

Some sample resources:

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

Enterprise DNA - Query Plan & Server Timings In DAX Studio

Actually your measure is roughly the opposite of the computation being tested in this recent article, so may be an interesting read:

https://www.sqlbi.com/articles/finding-products-without-sales-by-using-dax/

Re: DAX doing wrong with "not isblank over related fact" filter

sjoerdvn — Thu, 28 Sep 2023 08:30:40 GMT

Expanding on things said before..
I would leave the relationship as is, but change the measure to:

MinAttr = CALCULATE( MIN(D_Entities[Attribute]), F_Comments[Comment], CROSSFILTER(D_Entities[Name],F_Comments[Name],Both) )