cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter table based on another table

Hi,

 

I would like to filter numbers out based on if they exist in another table.

 

Example given.

 

Table 1 consist 

ID     Text Field

 

Table 2 consist 

ID

 

 

I'd like to filter Table 1, removing all the rows where the number from Table 2 ID exist.

 

I've tried by making a new table with Column = EXCEPT(VALUES(Table1[ID]);VALUES(Table2[ID])) 

 

Which gives me a new table that filters perfectly. however I need to keep my second column "Text Field".

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Filter table based on another table

@Rallerbabz,

Use the DAX below instead.

Table = CALCULATETABLE(Table1;EXCEPT(VALUES(Table1[ID]);VALUES(Table2[ID])))

1.JPG

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Filter table based on another table

@Rallerbabz,

Use the DAX below instead.

Table = CALCULATETABLE(Table1;EXCEPT(VALUES(Table1[ID]);VALUES(Table2[ID])))

1.JPG

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Filter table based on another table

Thanks alot! Works like a charm!

Highlighted
New Member

Re: Filter table based on another table

What if I want to keep the values in table 2 and filter out the remaining values from table 1?

Highlighted
Resolver I
Resolver I

Re: Filter table based on another table

I believe you can use INTERSECT instead of EXCEPT

Highlighted
Frequent Visitor

Re: Filter table based on another table

@v-yuezhe-msft  I am trying to do something similar, but do not want to build another table.  Instead, I want to calculate measures based on the filter that will be visualized on a matrix in a report. For example:

Table A

IDCostRevenue
1124
1235
1337
14511

 

Table B (discontinuation)

ID
11
12

 

Matrix on report:

 Before DiscAfter Disc
Total Cost138
Total Revenue2718

 

Any recommendations?  Thank you!!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors