Open one table by clicking another table cell

[anv]

Hi,
I am a newbie to Power BI and I am trying to open Table 2 by clicking table 1 cell.

The table looks like:

            Table 1                                                                         Table 2
IDCode   |    User   |   Date                       IDCode   |   FirstName   |   SupplyCode   |   Country

1                Mat        2018-01-01                   1             Mat                00110011           USA

2                John       2017-01-09                   1             Mat                98765445           Australia

1                Mat        2018-04-08                   1             Mat                8765779             Canada

3               Jazz         2019-01-01                   3             Jazz                6666666             USA

So basically when I click 'IDCode' 1 in Table 1, Table 2 should show all the three values with 'IDCode' 1 in Table 2.

I created a measure just to first get the firstName in Table 2

My query is

FirstName = LOOKUPVALUE('Table2'[FirstName], 'Table2'[IDCode], SELECTEDVALUE('Table1'[IDCode]))

But this query returns only distinct value, not sure how do I get all the three values.

I also tried creating a custom column

FirstName = CALCULATE (VALUES('Table2'[FirstName]),FILTER ( 'Table2', 'Table2'[IDCode] = SELECTEDVALUE(Table1[IDCode] )))

But this is giving me circular dependency detected error.

Is there any other way to get the expected result?

[v-cherch-msft · Accepted solution]

Hi @anv

You may create the relationship as many to many in relationship view.Attached the sample file.

Regards,

Cherie