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.
Thanks in advance!
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:
The number column is there because at first I thought it may have been rejecting it for it being a 1-column table.
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"
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?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.