Filtering rows in one table by values from another table

[SG_17]

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!!

[v-rzhou-msft]

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. 

[amitchandak]

@SG_17 , Try treatas

https://docs.microsoft.com/en-us/dax/treatas-function

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[edhans]

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.

[SG_17]

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

[SG_17 · Accepted solution]

I figured it out: 

CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))