Filter all instances in second table by clicking on single value in first table

[Anonymous]

Hi all,

I'm relatively new to Power BI and this problem sounds simple, but I can't solve it.

I have a table1 that looks about like this, which I use for my "search table" and for my "results table" inside my dashboard (additional information that shouldn't confuse, but maybe is relevant: this table1 is the result of a drillthrough):

GL_Journal_ID   Key    Value

         10               1          50

         10               2          70

         12               3          52

         12               4          81

         14               5          89

         14               6          22

Now I want to be able to click at a row which contains the 12 in my search table (so the row with key 3 or 4) and have the following in my results table:

GL_Journal_ID   Key    Value

         12               3          52

         12               4          81

But what I get is either this:

GL_Journal_ID   Key    Value

         12               3          52

or this:

GL_Journal_ID   Key    Value

         12               4          81

My workaround with a third table in between with only the GL_Journal_ID as a single column is very confusing to third parties and doesn't look too good.

I'd really appreciate some help 😕 

[v-lili6-msft]

hi  @Anonymous 

You could create a separate slicer or table visual and drag [GL_Journal_ID] into it for interaction.

here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

I have not tested but try like.

Create a Table with a distinct journal ID. and join it with both tables. Make to join bi-directional. And then try.

[Anonymous]

---

@amitchandak wrote:

I have not tested but try like.

Create a Table with a distinct journal ID. and join it with both tables. Make to join bi-directional. And then try.

---

I duplicated table1 into table1 and table2 outside of Power BI and loaded them in. Then I created a table3 which consists of unique GL_Journal_ID values. In Power BI I joined table1 and table 2 bi-directional to table3 on the GL_Journal_ID. In the search table I added the GL_Journal_ID column from table3 and now it works.

But isn't there a way without copying all of your data? Or is there even a way to copy a table in Power BI?

[amitchandak]

I did not get your last update.

But you can do that in power bi like

Table3 = distinct(union(all(table1[Gl account]),all(table2[Gl account])))

another option

join Table1 and table2 M-M on GL account and have a bidirectional join. This can also work. But it will impact other relations. You can try