Filters Not Working with Many to Many Relationship

[srennie]

I am having real issues with the two data sets I’ve got to work with – I will try to describe the problems in as much detail as I can because it won’t be possible to provide a sample file.

I have one set of data that contains all the order data and another than contains all the defect data. The obvious link between the two would be the ‘order number’ column, however, the first problem discovered was the order number wasn’t always populated in the defect data meaning when the visuals were filtered by supplier some of the defect were missing and the measures returning incorrect values.

Instead, I’ve linked the two data sets by ‘Supplier Name’ column using a many to many relationship. This results in the correct measures when no filters are applier or the supplier filter applied but when I try to filter on something else, ‘site name’ for example it either won’t pick up all defect data or all order data because one supplier can supply multiple sites.

All columns I’m attempting to filter on are present in both data sets but it obviously isn’t linked properly. Hopefully these is a good solution to handle this less than perfect data sets. 

[johnt75]

One possibility might be to introduce a dummy row into the order data, with an order number of e.g. -1. You could introduce such a row in Power Query by just using the Enter Data functionality and then appending this query to your existing query. Then you could use Power Query again to replace all missing values of order number in the defects data, changing null to -1.

[srennie]

@johnt75 for the rows with the dummy order number how would Power BI know how to filter the data by supplier etc?

[johnt75]

I was thinking you would put a dummy supplier in the dummy row, but if the supplier name exists in both tables then you could create a dimension table like

Supplier dimension =
DISTINCT (
    UNION (
        ALLNOBLANKROW ( 'Orders'[Supplier] ),
        ALLNOBLANKROW ( 'Defects'[Supplier] )
    )
)

then create a one-to-many relationship from this new table to both existing tables. use the column from the new dimension table in any visuals or filters and it should filter both tables.

[srennie]

@johnt75 I've created a dimension table as you suggested however, it won't let me create a one-to-many relationship from this new table to both existing tables. I'm getting the error 'the cardinality you selected isn't valid for this relationship'

[johnt75]

You're using ALLNOBLANKROW, not VALUES ?

And you're sure that you have the many and one settings on the right table when you try to create the relationship ?

Can you share a PBIX with any confidential information removed ?

[srennie]

@johnt75 I've been able to create a sample file with one supplier as an example of the issue I'm having with the site code filter. Any help is much appreciated. 

Link here.

[johnt75]

I've been able to use the same technique to create a site code dimension and link that to both tables.