Forum Discussion

SG_17's avatar
SG_17
Frequent Visitor
6 years ago
Solved

Filtering rows in one table by values from another table

Hi, relatively new user trying to complicate my own life with difficult tasks. lol.  I need some help with filtering out rows from a data table (Table A) based on matching IDs from a lookup table (Ta...
Need Help
  • SG_17's avatar
    SG_17
    6 years ago

    I figured it out: 

    CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi SG_17 

edhans’s way is good. And you may try my way.

I build a table as below.

And build two measures to achieve your goal.

Total cost = IF(MAX('Table'[Statue])="Before",SUM(A[Cost]),CALCULATE(SUM(A[Cost]),FILTER(ALL(A),NOT A[ID] IN VALUES(B[ID]))))
Total Revenue = IF(MAX('Table'[Statue])="Before",SUM(A[Revenue]),CALCULATE(SUM(A[Revenue]),FILTER(ALL(A),NOT A[ID] IN VALUES(B[ID]))))

Use measures to build a matrix, and select show on rows in Values.

Result:

You can download the pbix file from this link: Filtering rows in one table by values from another table

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.