cancel
Showing results for
Did you mean:
Frequent Visitor

## Setting filter where two columns (different tables) match

Hi,

I have one table that contains product information, all unique values:

01-Hammer

02-Screwdriver

03-Wrench

I have two other tables, one that contains customer info

Customer A, 01-Hammer, 5 orders

Customer B, 03-Wrench, 2 orders

Customer C, 03-Wrench, 14 orders

Customer A, 02-Screwdriver, 20 orders

and another that contains external market data :

CityA, 01-Hammer, 80 orders

CityB, 01-Hammer, 35 orders

CityA, 02-Screwdriver, 130 orders

City B, 02-Screwdriver, 70 orders

Both of these tables connect to my first table in a many to one relationship. I pull these last two tables together to get our data vs market data:

01-Hammer, 200 orders (our company), 1500 orders (market)

02-Screwdriver, 100 orders (our company), 1000 orders (market)

03-Wrench, 100 orders (our company), 500 orders (market)

And created a measure for our data and market data to calculate percentage of total products, as well as the difference:

01-Hammer. 200 orders (our company), 50%, 1500 orders (market), 20%, 30%

02-Screwdriver, 100 orders (our company), 25%, 3000 orders (market), 30%, -5%

03-Wrench, 100 orders (our company), 25%, 5000 orders (market), 50%, -25%

The measures I used:

`Measure1 = divide(CALCULATE(Count(OurData[ID])),calculate(COUNT(OurData[ID]),all('OurData'[ProductInfo])))`
`Measure2 = divide(CALCULATE(Count(Market[ID])),calculate(COUNT(Market[ID]),all('Market'[ProductInfoField])))`
`Measure3 = Measure1 - Measure2 `

Now, it pulls the correct values where the two ProductInfo match up, but given it's essentially a many to many relationship, the output is not what we want. In the above example, istead of the 3 rows that I want returned, it returns 9:

01-Hammer, 01-Hammer (correct info)

01-Hammer, 02-Screwdriver

01-Hammer, 03-Wrench

02-Screwdriver, 01-Hammer

02-Screwdriver, 02-Screwdriver (correct info)

02-Screwdriver, 03-Wrench

03-Wrench, 01-Hammer

03-Wrench, 02-Screwdriver

03-Wrench, 03-Wrench (correct info)

So, is there some way I can add a filter so only the matching values are returned? Basically an inner join through the visual's filter box? Thanks for reading!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Setting filter where two columns (different tables) match

Hi @MarkJ09,

For many to many relationship tables, you can't direct drag then into same visual, you need to drag bridge table column(Product[ID]) to link help power bi analysis columns.

In addition, I'd like to suggest you modify a formula to use bridge table column to filter on related tables:

```Measure1 =
DIVIDE (
CALCULATE ( COUNT ( OurData[ID] ), VALUES ( Product[ID] ) ),
CALCULATE ( COUNT ( OurData[ID] ), ALLSELECTED ( 'OurData' ) )
)

Measure2
= DIVIDE (
CALCULATE ( COUNT ( Market[ID] ), VALUES ( Product[ID] ) ),
CALCULATE ( COUNT ( Market[ID] ), ALLSELECTED ( 'Market' ) )
)
```

If above not help, please share a sample pbix file to test and coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team

## Re: Setting filter where two columns (different tables) match

Hi @MarkJ09,

For many to many relationship tables, you can't direct drag then into same visual, you need to drag bridge table column(Product[ID]) to link help power bi analysis columns.

In addition, I'd like to suggest you modify a formula to use bridge table column to filter on related tables:

```Measure1 =
DIVIDE (
CALCULATE ( COUNT ( OurData[ID] ), VALUES ( Product[ID] ) ),
CALCULATE ( COUNT ( OurData[ID] ), ALLSELECTED ( 'OurData' ) )
)

Measure2
= DIVIDE (
CALCULATE ( COUNT ( Market[ID] ), VALUES ( Product[ID] ) ),
CALCULATE ( COUNT ( Market[ID] ), ALLSELECTED ( 'Market' ) )
)
```

If above not help, please share a sample pbix file to test and coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Highlighted
Frequent Visitor

## Re: Setting filter where two columns (different tables) match

The new measures did the trick. Thank you!