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