cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 54 members 908 guests
Please welcome our newest community members: