Forum Discussion
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 (Table B). I would prefer to do this with a measure rather than through a creating a new table to minimize data redundancy. I would like to have both with and without the filter to be shown in different columns on the report. I think this can be better explained via visualization in my example below:
Table A
| ID | Cost | Revenue |
| 11 | 2 | 4 |
| 12 | 3 | 5 |
| 13 | 3 | 7 |
| 14 | 5 | 11 |
Table B (discontinuation)
| ID |
| 11 |
| 12 |
Matrix on report:
| Before Disc | After Disc | |
| Total Cost | 13 | 8 |
| Total Revenue | 27 | 18 |
Any recommendations? Thank you!!
I figured it out:
CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))
5 Replies
I would just bring in one table. Merge Table B to Table A and create a Discontinued column. Then you can have very simple measures that just total revenue and costs with this:
Total Revenue = SUM('Table A'[Revenues]) Total Cost= SUM('Table A'[Cost])See my PIBX attached. Note that in the Matrix settings I had to tick the Show the Values on the Rows setting. That is not the default. You can see in the Power Query (Transform Data) part where I merged the tables to just create a True/False field for the Discontinued. You can change that formulat to show "Discontinued" and "Not Discontinued" vs the true and false values there.
Thank you for your help. I'm not sure if that can work though. Table A has multiple instances of the ID that are added daily (the table is updated weekly), while Table B has one instance of each ID and changes (not necessarily all IDs) quarterly. Timing would also play a factor in how the discontinuation is applied. To provide a little more clarity, I used the term discontinuation, but really we offer different plans that provide for the ability to opt-out of specific products based on a given plan for reduced costs. This opt-out product list (plan) is generated quarterly. The goal of this analysis is to see the change in costs and revenues when a client wants to opt-out to determine overall pricing. The way that we calculate this is that we apply this opt-out plan to a clients previous product order history (Table A is total product orders) to tell them the savings from opting out. Accordingly, I would prefer to just update the data tables (Table A weekly and Table B quarterly), rather than having to re-create merged tables.
Here's a little more clarity via example:Table A
Order_date Product_ID Cost Revenue 1/1/2020
123 5 8 1/2/2020 111 7 15 1/3/2020 231 10 20 1/3/2020 222 3 5 1/4/2020 123 5 8 Table B
Year Quarter Product_ID 2020 Q1 123 2020 Q1 222 Output
Total cost Total revenue Opt-in 30 56 Opt-out 17 35 I figured it out:
CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))
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.
