Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jvirgi
Helper III
Helper III

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

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

Table 1:

AttributeLevelAttributeValueNPS

Brand

Apple8
BrandSamsung9
ProductiPhone 1310
ProductiPhone 120
ProductiPad Pro14
ProductGalaxy S2222
ProductGalaxy S2116

 

Table 2: 

BrandProductColorsPrice
AppleiPhone 13Red1099
AppleiPhone 12Blue999
AppleiPhone 12Silver999
AppleiPad ProGray799
SamsungGalaxy S22Black899

 

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:

 

jvirgi_0-1668640209002.png

Here is the example PBIX

1 ACCEPTED SOLUTION

Hi @jvirgi ,

 

Please transform product table in Power Query.

 

1. Select Brand and Product column - Unpivot columns

vyadongfmsft_2-1668664644476.png

 

2. You will get a table like this:

vyadongfmsft_3-1668664728113.png

 

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

vyadongfmsft_4-1668664815627.png

 

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

vyadongfmsft_5-1668664881411.png

vyadongfmsft_6-1668664908423.png

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.

View solution in original post

3 REPLIES 3
jvirgi
Helper III
Helper III

@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?

Hi @jvirgi ,

 

Please transform product table in Power Query.

 

1. Select Brand and Product column - Unpivot columns

vyadongfmsft_2-1668664644476.png

 

2. You will get a table like this:

vyadongfmsft_3-1668664728113.png

 

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

vyadongfmsft_4-1668664815627.png

 

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

vyadongfmsft_5-1668664881411.png

vyadongfmsft_6-1668664908423.png

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
Community Support
Community Support

Hi @jvirgi ,

 

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

vyadongfmsft_0-1668650635630.png

 

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

vyadongfmsft_1-1668650951465.png

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.