Show only matching values from multiple tables

[Zilver]

Hi all,

I have 2 tables like this :

Table 1

Ord_Number	Status
10	D
20	D
30	D

Table 2

Ord_Number	Status
40	U
50	U

and I have Table 3 have so many numbers including some values from table 1 & 2 like this:

Table 3

Ord_Number	Status
10	D
15	D
20	D
45	U
50	U

So what i'm trying to do to show all the data who matched in table 1 and 2 like this:

Table 3

Ord_Number	Status
10	D
20	D
50	U

Thanks all

[dax · Accepted solution]

Hi @Zilver , 

You could create a table to union table1 and table2 's column, then create relationship between table3, then use not blank in filter, which will work.

Or you also could use measure to achieve this goal(don't create relationship between new table and table 3). You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[amitchandak]

Table 3, the last one is common of 1 and 2. But one and two had nothing common.

But refer

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Zilver]

So far what I did is I create table 3 and I put table 1&2 in (filter on this page) and I select filter type : basic filtering "select all" for both table. and ofc I create relationship between them as (many to one)(both).

and didn't work 😞

please if someone could help me how to fix that I really appreciate it.

[amitchandak]

What is the logic to achieve this table

Table 3

Ord_Number	Status
10	D
20	D
50	U

[Anonymous]

I do have the same problem. In my specific case I use names instead of numbers. Imagine data since 2019 from my team. In all this team some people left the team. I have a table with EVERYONE since 2019 and another with only the ones that are in the team right now. I want to use my second table to filter he first one so only the values of the second will appear.

So in resume. The first table as thousand of rows and columns. And the second one only has the names that I wat to "filter".

Names	Age	ID	etc	etc	etc
Mike	74	1325	etc	etc	etc
Freddy	26	1326	etc	etc	etc
Carl	99	7653	etc	etc	etc
Mark	34	1746	etc	etc	etc
John	57	9816	etc	etc	etc
Mary	86	2749	etc	etc	etc
Amitchandak	30	5472	etc	etc	etc

Names

John

Mary

Mark

Amitchandak

My final result should look like this

Name	Age	ID	etc	etc	etc
Mary	86	2749	etc	etc	etc
Mark	34	1746	etc	etc	etc
Amitchandak	30	5472	etc	etc	etc
John	57	9816	etc	etc	etc

Also , this should be displayed in a bar chart

Hope it helps