How to Connect 2 tables: attribute to use for the relationship varies by row

[jvirgi]

I have 2 tables I'm trying to figure out how to connect with a relationship.  

Table 1:

AttributeLevel	AttributeValue	NPS
Brand	Apple	8
Brand	Samsung	9
Product	iPhone 13	10
Product	iPhone 12	0
Product	iPad Pro	14
Product	Galaxy S22	22
Product	Galaxy S21	16

Table 2: 

Brand	Product	Colors	Price
Apple	iPhone 13	Red	1099
Apple	iPhone 12	Blue	999
Apple	iPhone 12	Silver	999
Apple	iPad Pro	Gray	799
Samsung	Galaxy S22	Black	899

Then in the dashboard I want to be able to filter for colors / Price or products and see the NPS scores for the level i select.  Heres an example:   I would only want to see the highlighted row in this table:

Here is the example PBIX

[jvirgi]

@v-yadongf-msft thanks. So I think that works when slicing it for specific products, but then if I switch to want to look at the brand level, I don't think that relationship still works then since that would require a different relationship to the brand column.  Any ideas on that?

[v-yadongf-msft · Accepted solution]

Hi @jvirgi ,

Please transform product table in Power Query.

1. Select Brand and Product column - Unpivot columns

2. You will get a table like this:

3. Create relationship between [AttributeValue] column and [Value] column.

4. You can filter at the brand level and product level. I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft]

Hi @jvirgi ,

Please create one to many relationship between AttributeLevel column and Product column.

Create a table like this and you will get the result you want:

Please note: For one-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). Notice that when the cardinality type includes a "one" side, that filters will always propagate from that side.

For more information, please refer to:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.