Multiple Relationships with Many to Many

[kirbynguyen]

I am using a identifier table to try to display the values from another table. Here are the tables:

date	name	value
1-Jan	Red	14
2-Jan	Red	31
3-Jan	Red	43
4-Jan	Red	78
5-Jan	Red	24
1-Jan	Blue	65
2-Jan	Blue	54
3-Jan	Blue	24
4-Jan	Blue	54
5-Jan	Blue	24
1-Jan	Green	75
2-Jan	Green	22
3-Jan	Green	64
4-Jan	Green	43
5-Jan	Green	33
1-Jan	Purple	23
2-Jan	Purple	12
3-Jan	Purple	55
4-Jan	Purple	67
5-Jan	Purple	89

name	name2	name3
Red	Blue	Purple
Blue	Purple	Red
Green	Purple	Red

What I'm trying to display using this example is when I select the first row on the identifier table, I want to display the values for name, name2, and name 3 from the data table. To do this, I have a main relationship (1-many) from ID[name] to Data[name], and also 2 disabled (many-many) relationships for ID[name2] to Data[name] and ID[name3] to Data[name]. I've tried creating DAX to use the relationships for name2 and name3, but it is giving the exact same values for name one.

The result I want for the first row (Red, Blue, Purple) is to show the Red, Blue, and Purple lines on 3 separate graphs when I selected the unique [name].

How do I solve this?

[v-henryk-mstf]

Hi @kirbynguyen ,

Whether the advice given by @lbendlin  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[lbendlin · Accepted solution]

Identifier:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xC8MgEIbh/3JzOujdabp2KXQqXcWhELcQSiD/v6nxhK9k8eBB7tWUyF0e74UGepVpP51QHhJ5QHYVGVC4ogDGsaIC+mOnhW7zVvYRFEpNVSDVtG2Q07t6etdq97WU34yYM/YeesYBg8btz/rHzJB8butnrk9haHZ3GO2uCtXuIUK2+3ilnL8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, name = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"name", type text}, {"value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"name"}, Reference, {"name"}, "Reference", JoinKind.LeftOuter),
    #"Expanded Reference" = Table.ExpandTableColumn(#"Merged Queries", "Reference", {"name2", "name3"}, {"name2", "name3"})
in
    #"Expanded Reference"

Reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRcsopTQVSAaVFBTmpSrE60WgiOmB1IHH3otTUPAyJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, name2 = _t, name3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"name2", type text}, {"name3", type text}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".