Setting filter where two columns (different tables) match

[MarkJ09]

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!

[v-shex-msft · Accepted solution]

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.

Notice: do mask on sensitive data and upload it to onedrive or google drive, then share link here.

Regards,

Xiaoxin Sheng

[MarkJ09]

The new measures did the trick. Thank you!