Forum Discussion
Filters Not Working with Many to Many Relationship
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.
johnt75 for the rows with the dummy order number how would Power BI know how to filter the data by supplier etc?
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.
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'
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 ?