Forum Discussion
Relate one column to multiples
You can still count things when you ID link them with relationships. That's the premise of data modeling - if you want to be successful with Power BI, you have to use the dimensional modeling aspect of the tool. You can make a measure that counts the item IDs in the primary table and then drop in the strength value from the second table in the visual this way. If you have them unpivoted in the second table, that's totally fine - just make sure to keep the item ID in there for the relationship. People tend to use "unpivot other columns" to do this, where you leave just the item ID and the 4 strengths columns, select item ID, right click -> unpivot other columns option. That gets the strengths labels into a single column and leaves the ID for the relationship.
Unfortuantely I can't link the ID as the new table is counting the number of occurrences a particular selection occurs across multiple columns in the main table and across multiple rows in the main table.
What I have is, for example, in the main table, Strenghts 1, Strengths 2, Strengths 3 and Strengths 4. In order to see how often "Price" might have been given as a strength, I have created a new table using UNION that collates this information from each of the four columns so I can do a COUNT on "Price".
What I need is to be able to select "Price" and link it back to customer, or product, if a tender was won or lost.
You can still count things when you ID link them with relationships. That's the premise of data modeling - if you want to be successful with Power BI, you have to use the dimensional modeling aspect of the tool. You can make a measure that counts the item IDs in the primary table and then drop in the strength value from the second table in the visual this way. If you have them unpivoted in the second table, that's totally fine - just make sure to keep the item ID in there for the relationship. People tend to use "unpivot other columns" to do this, where you leave just the item ID and the 4 strengths columns, select item ID, right click -> unpivot other columns option. That gets the strengths labels into a single column and leaves the ID for the relationship.
Thank you, what I didn't realise was that using SELECTCOLUMNS in UNION I could pull through more than one column's information. I have modified that code to create two columns, including the ID reference allowing me to build the relationship.