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

[DmitryKo]

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:

[sjoerdvn]

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

[DmitryKo]

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

1. Use the original measure (with ISBLANK), but change D_Entities to F_Comments cross-filter direction to Both;
2. Use the measure suggested by @nirali_arora, except for the ALL() part that would remove much-needed context;
3. 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?

[OwenAuger]

@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/

[OwenAuger]

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

[nirali_arora]

You can use the following measure

MinimumAttribute =

MINX (

    FILTER (

        ALL ( 'D_Entities' ),

        COUNTROWS ( RELATEDTABLE ( 'F_Comments' ) ) > 0

    ),

    [Attribute]

)