Can't Relate Two Tables (Pictures Included)

[danielc7150]

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

Thanks in advance!

Dan

[Anonymous]

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.

[danielc7150]

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:

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

[mattbrice]

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" 

[danielc7150]

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

[Anonymous]

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!

[danielc7150]

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