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,[warehouse]AAAA didin't appears...
How should I correct it....?

■TABLE



■DAX

 

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",[PN])
var _result=calculate(countrows(TEST_),'TEST_'[PN] IN _Trg)
//var _result=calculate(countrows(TEST_),filter('TEST_','TEST_'[PN] IN {"X-0002-000-1000","X-0002-000-1002"}))  result expected

return
_result

 


■Result


■DATA
question_each warehouse.xlsx
I'm on the situation that can only use powerpivot by now,sorry for inconvinient...


that's all.Thank you for reading and your continuing support!

Best Regards,


2 Replies

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