Trouble creating relationship

[DRJ1994]

Good morning all,

I hope somebody can help with my query.

I am trying to create a relationship between two tables in my report.

I think the relationship should be 1 to Many, however PBI is only letting me add the relationship as many to many.

The relationship goes from 'Name' in the department table, to 'NeighbourhoodManager' in the voids table. The names in the department table should be unique and only occur once, and there will be more than one instance of the same names in the voids table. Does anyone know how I can fix this? The issue is when I am trying to filter and slice it is not letting me do it because of the cardinality with the relationship.

Any tips would be greatly appreciated.

Thanks in advance.

DRJ

[johnt75 · Accepted solution]

Is it possible that you have names in the Voids table which aren't in the Department table?

You could create a bridge table like

Name Bridge Table = DISTINCT( UNION(
ALLNOBLANKROW('Department'[Name]),
ALLNOBLANKROW('Voids'[Name])
))

and then create one-to-many relationships from that new table to both existing tables. Use the column from the bridge table in your visuals.