DAX syntax or a second many to many relationship?

[Tim_C]

Morning,

This is pretty much my first foray into BI, I'm fairly well experienced with Excel and Power Pivot/Query but I've not yet learnt VBA or DAX.

I have two queries in Power BI Desktop, the first is orders placed, the second is a list of vendors and the commodities they're approved for. On the table of orders, only one commodity is used per order. On the vendor commodity table each vendor can have between 1 and 20 commodities they're approved for with each approval having a status and a date it was issued.

I have a many to many relationship between the two tables, linked by vendor.

I am looking to report the commodity, approval status and date for each vendor with whom an order was placed in a time frame.

The issue I have is that the report lists all of the commodities the vendor is qualified for, not just the commodity used on the particular order.

The solutions I've tried include, a second many to many relationship between commodities on the two queries, creating one to many relationships by adding tables that list the unique  commodities and vendors (this is something I'd do normally in Excel to get around the no many to many relationships).

The way I've found to solve this is to have the commodity from each table on the report, export to Excel and filter where the two commodties match. I've tried to do this with DAX in Power BI but it isn't working for me. I've tried if(x=y,x) and exact(x,y) but I have trouble getting the second table into the formula. I have searched but couldn't find anything that said you can't link across tables and this is where I'm stuck. It should be a simple if statement and I assume I just don't know the syntax correctly. I've used the sum function but get an error that seems to imply it has to look at numbers, the data I'm looking at is text.

I haven't posted screen caps as in theory, I can't share the data. Sorry to be secret squirrel and all but I am guessing that examples are going to make this easier for me to convey. I'll try and work a sterile example and add to the post later today, as time allows.

Thanks for any help 🙂

Tim

ETA screen caps:

This first shows a filter of one vendor. The report has created duplicate lines because the query is pulling the "Commodity C" data where the relevant data is "Commodity L"

This screen cap is of the order info table for the 3 orders placed

And this is the info held on a separate table listing the commodities this vendor is qualifed for.

The two tables are linked by vendor:

[Greg_Deckler]

@Tim_C I don't see the screen shots. In general, you want to avoid direct many-to-many relationships like the plague and instead create a bridge table of unique values to tie the tables together instead. 

[Tim_C]

@Greg_Deckler

Screen caps added

🙂

[Tim_C]

Thanks Greg,

Sorry about the lack of screen shots, the data is not something I can share. I will add sterile data to the tables and post screen caps later today as I'm able.

Adding a second relationship did indeed make things worse.

I'll revisit creating something in each table to make the link but I am not sure it will work.