Forum Discussion

srennie's avatar
srennie
Regular Visitor
3 years ago

Filters Not Working with Many to Many Relationship

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...
Need Help
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
srennie
Regular Visitor
3 years ago

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's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      srennie
      Regular Visitor
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

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