Relate one column to multiples

[hdasmith]

I am creating a report which I would like to relate some SWOT analysis with products. The data comes from a Sharepoint List filled in from a Power Apps app. To allow multiple SWOT entries on one tendered opportunity, I have created four fields for each aspect of SWOT which are populated from identical drop-down lists, e.g. Price could be considered a Strength in one of four Strength fields, and it could also be a Weakness in another entry and could be entered in any one of four Weakness fields. I hope that's clear?!

To collate the data, I've created new tables in Power BI using the UNION function to merge all four Strenght fields, Opporuntity fields, and so on. While this allows me to count the most common Strength, Opportunity, Weakness or Threat, it breaks the relationship with other aspects, such as product or customer, so if I select a particular product from the graphs, Power BI won't now show me which Strenghts helped us with that product, or which weaknesses need most addressing.

Is there any way I can link the one collated Strengths table with the four Strength columns in the original data so that I can re-establish the relational data please?

[Anonymous]

Hi @hdasmith ,
Glad to hear you may have found a solution! If you're sure the issue has been resolved, could you mark this thread as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved. Thanks, and feel free to reach out if you need further help!

[hdasmith]

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.

[christinepayton · Accepted solution]

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. 

[hdasmith]

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.

[christinepayton]

I don't totally follow, but if you have multivalue columns in SharePoint, the best way to maintain the relationships is to expand just the single multivalue field in a separate query table, leaving in the item ID, and then relate it back to your other table using relationships. Usually you need to set the relationship to bidirectional (both) when you do this to get the filters to flow in the right direction. This will let you count items by whatever the multivalue field was in charts - you use the strength field for example from that second table.