Get a value from a related table

[Zyg_D]

This is my data:

I want to create the green column - a calculated column. It represents the name from max value of IDs AA and BB which both belong to the parent X. The relation is created between tables on the columns "ID". I tried this, but it failed:

Max_value = maxx(Table1[Name],[Parent]=[Parent])

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Parent", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, Table1, {"ID"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Value"}, {"Value"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table1", {"Parent"}, {{"Max", each List.Max([Value]), type number}}),
    Joined = Table.Join(#"Expanded Table1", "Parent", #"Grouped Rows", "Parent"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Duplicates", {"Max"}, Table1, {"Value"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table1", {"Name"}, {"Name"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Table2",{"Max"})
in
    #"Removed Columns1"

Hope this helps.

[v-alq-msft]

Hi, @Zyg_D 

You may create a calculated column as below.

Result = 
var _parent = [Parent]
var _maxvalue = 
CALCULATE(
    MAX(Table1[Value]),
    ALLEXCEPT(Table2,Table2[Parent])
)
return
CONCATENATEX(
    CALCULATETABLE(
        FILTER(
            ALL(Table1),
            Table1[Value] = _maxvalue&&
            RELATED(Table2[Parent]) = _parent
        )
    ),
    [Name],","
)

Result:

Best Regards

Allan

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

[parry2k]

@Zyg_D try this DAX expression by adding a column

Column = 
VAR __ids = CALCULATETABLE ( VALUES ( Table2[id] ), ALLEXCEPT ( Table2, Table2[Parent] ) )
RETURN
CALCULATE ( MAX ( Table1[Name] ), ALL ( Table1 ), TREATAS( __ids, Table1[Id] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Zyg_D]

---

@parry2k wrote:

@Zyg_D try this DAX expression by adding a column

Column = 
VAR __ids = CALCULATETABLE ( VALUES ( Table2[id] ), ALLEXCEPT ( Table2, Table2[Parent] ) )
RETURN
CALCULATE ( MAX ( Table1[Name] ), ALL ( Table1 ), TREATAS( __ids, Table1[Id] ) )

---

I was too quick to accept this as solution. In your fomula MAX looks at [Name] and returns it. But I want it to look at [Value] while returning [Name].