Forum Discussion
Matrix | relationship | slicer
I figured it out. The solution was to distinct join the columns from assignment and generaloverview to a new table OwningOffice. And use the values from there.
This article helped me figure it out:
https://community.powerbi.com/t5/Desktop/Getting-distinct-values-for-multiple-columns/td-p/31997
Hi JosefPrakljacic .
Thanks for answering.
These are the two tables I want to connect. But when I try to connect them, I'm only able to make a many to many connection, which I don't want. On one to may I get "the cardinality you selected isn't valid for this relationship".
If you would clean your "OwningOffice" Table because I think that "Bristol, Bristol" means just Bristol this would be a N:1 Relationship.
But looking at your data you wouldn't be finished. You need to clean your OwningOfficeAssignment Table also to reach a relationship. By cleaning i mean replacing the "office" suffix for instance.
Please use the PowerQuery Editor to clean up the columns and you may create a table afterwards based on your values.
I still get the same error when trying to relate them.
What I did:
I cleaned the owning office so that it's now the same values.
In the owning office assigment I only deselected a couple of coulumns which as irrelevant for the report. Still it should work shouldn't it?
Here are the values now:
I figured it out. The solution was to distinct join the columns from assignment and generaloverview to a new table OwningOffice. And use the values from there.
This article helped me figure it out:
https://community.powerbi.com/t5/Desktop/Getting-distinct-values-for-multiple-columns/td-p/31997