Combining two columns of different tables to make a filter

[Dunner2020]

Hi there,

I have two dimension tables which look like as shown in the following picture:

Table 1:

Table 2:

Code of Table 1 and Circuit code of Table 2 together make meaningful information in the fact table. Now I want to make a filter (drop-down list) that performs would be a combination of code of table 1 and Circuit code of table 2 to filter the data of the fact table.  Is it possible in Power BI?

[Anonymous]

Hi I'm not sure I fully understand your point, but I have below 2 options for you:

this is for filtering on chart, it shows this:

And if I use DAX, this is what I have:

Updated Circuit Code = CALCULATE(MAX('Table 2'[Circuit Code]),

FILTER('Table 2','Table 2'[Circuit Code]<>BLANK()),

RELATEDTABLE('Table 1'))

Data is what you provided. Thanks!

[v-janeyg-msft · Accepted solution]

Hi, @Dunner2020 

It’s my pleasure to answer for you.

According to your description, I think you can merge the two tables to a new table with two columns then use.

First you need to create two index columns for the two tables.

Like this:

Here is my sample .pbix file. Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@Dunner2020 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Dunner2020]

@amitchandak , For example, I want to have drop-down list filter that shows the combination of both columns from the two different tables as shown in the following figure: