cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielc7150
Frequent Visitor

Can't Relate Two Tables (Pictures Included)

I'm trying to relate two tables: one containing a list of all jobs released for a maintenance company, and another showing stock levels. Not all job categories relate to stock levels, but some relate to more than one. I was wondering if there was a way for the job category to be selected on one graph, and it highlights all possible stock on another.

The large database with all entries on - relevant columns are maintenance category and maintenance item.The large database with all entries on - relevant columns are maintenance category and maintenance item.The stock table, with all stock items under 'stock item'The stock table, with all stock items under 'stock item'The circle area is what i want to become highlighted when 'white goods' is selected on the graph on the right.The circle area is what i want to become highlighted when 'white goods' is selected on the graph on the right.

Thanks in advance!

Dan

6 REPLIES 6
Anonymous
Not applicable

Hi @danielc7150, when creating a relationship, one of the columns needs to be a list of distinct values. In this example, the values for Maintenance Item reports multiple times on each table.

 

I would create a separate table that has the distinct list of maintenance categories. Then, connect this table to the 2 tables below. Use the Maintentance Category column from your new table in your charts, and you should be able to filter as desired.

Thanks for the reply @Anonymous.

 

Tried this, and it doesn't let me link the two tables, claiming that one still needs to have unique values.

Here is the new table that I added:

qwerty.PNG

The number column is there because at first I thought it may have been rejecting it for it being a 1-column table.

Any ideas?

 

Dan

 

like @Anonymous says, you can't have columns on both sides of the relationship with duplicate values.  In your list, you could link to the Number column because they are all unique.  But can't use Maintenance Category on "1" side of relationship as it has repeated items "White Goods", "Inventory Items" 

The top right diagram (from main job list) is unable to interact with bottom right diagram (Stock levels)The top right diagram (from main job list) is unable to interact with bottom right diagram (Stock levels)Showing relationshipsShowing relationships

Anonymous
Not applicable

Hey @danielc7150 - just to confirm you are using the "Maintenance Category" field from the Sheet1 table on all charts, correct? If you could post your pbix file, that would be super helpful!

Thank you very much to the both of you! Had seen this solution already but for some reason my mind overlooked the fact that their were duplicates, due to the fact that on the original list of stock there were multiple entries of both 'inventory' and 'white goods' separated by others! Silly me!

 

One last question, @Anonymous / @mattbrice, if im using a bar chart for the categories on my main document, and select one to highlight it, it doesn't highlight on the stock level. Is this because highlighting can only work in the direction of 1-to-many, or is there a way around it?

 

Thanks again!

Dan

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.