DAX for all related records meet criteria

[jeffshieldsdev]

I have ORDERS, which have LINES, which can have one or more ERRORS.

Those ERRORS can be ADDRESSABLE, or not.

I'm trying to write a measure that returns LINES WITH ONLY ADDRESSABLE ERRORS.

I can write for ADDRESSABLE_LINE_ERRORS and NON-ADDRESSABLE_LINE_ERRORS--but these are totals…I need to know where all LINES in the ORDER are all ADDRESSABLE.

I'm also trying to do this in a dual-storage/DirectQuery model.

Here's a PBIX: https://drive.google.com/file/d/1tU89i4yzGdNehhxm4j-ySvmCsMIJ9AVC/view?usp=sharing 

[ThxAlot · Accepted solution]

[jeffshieldsdev]

Well, it's working great with my Import tables, but with my dual-storage/DirectQuery tables, I'm getting this error:

I may have to move this calcuation upstream to the DB.

[jeffshieldsdev]

Two things helped this:

• Using COUNT() instead of COUNTROWS()
  • COUNT() uses a column instead of a table, so I just used 'LINE_ERRORS'[ERROR_ID]
• No value was returned when using NOT ERRORS[IS_ADDRESSABLE] in DirectQuery, so I switched to ERRORS[IS_ADDRESSABLE] = FALSE() and that worked.

[jeffshieldsdev]

Very cool, thanks!

I was able to adapt to this measure to give me Lines with only addressable errors:

__ADDRESSABLE_LINES = CALCULATE(
    [__LINES],
    ERRORS[IS_ADDRESSABLE],
    LINE_ERRORS
)

EDIT: I was seeing false positives with the above, so created these instead:

__LINE_HAS_ONLY_ADDRESSABLE_ERRORS = CALCULATE(
    ISEMPTY( ERRORS ),
    NOT ERRORS[IS_ADDRESSABLE],
    LINE_ERRORS
)

__NON-ADDRESSABLE_LINES = CALCULATE(
    [__LINES],
    NOT ERRORS[IS_ADDRESSABLE],
    LINE_ERRORS
)

and then the measure I ultimately need:

__ADDRESSABLE_LINES = [__LINES] - [__NON-ADDRESSABLE_LINES]