cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Roha83 Member
Member

Matrix | relationship | slicer

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: bilde.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Roha83 Member
Member

Re: 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

View solution in original post

5 REPLIES 5
JosefPrakljacic Established Member
Established Member

Re: Matrix | relationship | slicer

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

Roha83 Member
Member

Re: Matrix | relationship | slicer

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

 

bilde.png

JosefPrakljacic Established Member
Established Member

Re: Matrix | relationship | slicer

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.

 

2019-09-13 15_45_31-Reply to Message - Microsoft Power BI Community.png

Roha83 Member
Member

Re: Matrix | relationship | slicer

@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:

bilde.png

Roha83 Member
Member

Re: 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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 473 members 3,688 guests
Please welcome our newest community members: