Matrix | relationship | slicer

[Anonymous]

Hi,

I'm making a matrix with a lot of info in it.

So my problem is that I have two tables with location. And I'm using one of them in my matrix. How to makt this dynamic? I was unable to make a relation between these two columns.

My matrix:

as you can see assignment by new etc are showing the same numbers, due to that they don't filter towards owning office.

Cause in the assignment table I have Owning office assignment.

[JosefPrakljacic]

Hello Roha,

it's obvious that your relationships are not maintained in the data model. For this reason we see the repetition of your values.

Without a correct relationship between the two tables, these values will always be repeated.

To help you here it would be nice if you would give us sample tables that show the structure of real tables.

So we could help you with data modeling and your problem.

Without your SampleData Input it would be just meaningless guessing

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[Anonymous]

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".

[JosefPrakljacic]

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.

[Anonymous]

@JosefPrakljacic 

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:

[Anonymous · Accepted solution]

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