Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jeffshieldsdev
Solution Sage
Solution Sage

DAX for all related records meet criteria

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 

jeffshieldsdev_0-1695392431989.png

jeffshieldsdev_1-1695392695503.png

 

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

ThxAlot_0-1695403091884.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

4 REPLIES 4
ThxAlot
Super User
Super User

ThxAlot_0-1695403091884.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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

jeffshieldsdev_0-1695418205981.png

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

 

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.

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]

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors