topic DAX for all related records meet criteria in DAX Commands and Tips

DAX for all related records meet criteria

jeffshieldsdev — Fri, 22 Sep 2023 15:36:14 GMT

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

Re: DAX for all related records meet criteria

ThxAlot — Fri, 22 Sep 2023 17:16:54 GMT

Re: DAX for all related records meet criteria

jeffshieldsdev — Fri, 22 Sep 2023 19:15:58 GMT

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]

Re: DAX for all related records meet criteria

jeffshieldsdev — Fri, 22 Sep 2023 21:31:06 GMT

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.

Re: DAX for all related records meet criteria

jeffshieldsdev — Mon, 25 Sep 2023 17:44:49 GMT

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.