Select Table rows based on another Table

[Anonymous]

Hi All,

I have the below requirement. Please check and help

I have 2 Tables ,Table 1 and Table 2 and i have requirment. when a row in Table 1 is selected, ONLY Matching values (Names)of Table 2 should be displayed. Ex: if i select any row which has Name=James in Table1, the Values which matches James in Table 2 should be selected and displayed,

Table 1:

Name	Amount	Date	Status
james	2000	1/10/2000	Approved
james	100	10/11/2000	rejected
james	1200	8/12/2000	Approved
james	5000	20/03/2021	Approved
james	1500	13/04/2021	Approved
Ajay	2000	1/10/2020	Approved
Ajay	2100	10/11/2000	rejected
Ajay	2500	8/12/2000	Approved
Ajay	15000	20/03/2021	Approved
Ajay	2500	13/04/2021	Approved
Ajay	1500	15/04/2021	rejected

Table 2:

Name	Year	Approved Amount
James	2020	3200
James	2021	6500
Ajay	2020	4500
Ajay	2021	17500

[v-chenwuz-msft]

Hi Ajay,

There two ways to solve this question. Creating a relationship or using measure

A:creating a relationship

you can create a relationship between these tables and you can follow along with my steps.

1 after you create these tables you need, go to the model view

Under the home tab, select the Manage relationships

2 Then it will show the manage relationship windows and click new icon.

3 In the Create relationship dialog box, in the first table drop-down list, select a table. Select the column you want to use in the relationship.

4 In the second table drop-down list, select the other table you want in the relationship. Select the other column you want to use, and then elect OK.

5 then go to the report view, create two table visuals like these.

Under such conditions, if you select any row which has Name=James in Table1, the Values which matches James in Table 2 should be selected and displayed.

If you would like to know more about how to create and manage relationships, please see the following articles.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

B:using a measure

Prerequisites: there no relationship between these tables.

1 After you create table, create a new measure

Measure 1 =

            Var _x=selectedvalues(‘table 1’[Name])

            Return

            If( _x=max(‘table’[Name]),1,0)

2 then put Measure 1 into table 2’s Filters, setting the Measure 1 show items when the value is 1 as the following image show and apply filter.

3 Then you select any one of name in table 1, the table 2 will display matched name in table 2.

Best Regards

Community Support Team _ chenwu zhu

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

[dmf]

I want to add this does not work for table visuals. The tables must be queries themselves and not a visual table for this to work. 

[Mohammad_Refaei]

You can create a many-to-many relationship between the name fields with bi-directional filtering thoug it is not the best approach.

It is better to create a name table and use it as attached

[Greg_Deckler]

@Anonymous Use a relationship between the two tables. If necessary, create a bridge table Table = DISTINCT('Table 1'[Name])