Forum Discussion

ohnothimagain's avatar
ohnothimagain
Helper I
1 year ago

How to filter with dynamic Array based on calculation

Hello everyone. I need to filter this table with conditions;  summarize [PN] and except [@diff]values lower than zero. I tried some practices and spent hours,but filter affected to each row,[wareho...
ahadkarimi's avatar
ahadkarimi
Solution Specialist
1 year ago

Hi ohnothimagain, give this a try, and if you encounter any issues, let me know.

test11 :=
VAR _sum =
    SUMMARIZE(
        'TEST_',
        'TEST_'[PN],
        "@Qty", SUM('TEST_[Qty]'),
        "@Req", SUM('TEST_[Req]')
    )
VAR _add =
    ADDCOLUMNS(
        _sum,
        "@diff", [@Qty] - [@Req]
    )
VAR _ftd =
    FILTER(
        _add,
        [@diff] > 0
    )
VAR _Trg =
    SELECTCOLUMNS(
        _ftd,
        "@PN", 'TEST_'[PN]
    )
VAR _result =
    CALCULATE(
        COUNTROWS('TEST_'),
        'TEST_'[PN] IN _Trg
    )

RETURN
    _result

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

  • ohnothimagain's avatar
    ohnothimagain
    Helper I
    1 year ago

    Thank you for replying fast,I do appriciate!
    but result was the same.. -1000's warehouse AAAA didn't appear.
    (seemed to be still remained filter context to each row )