Table calculated with firstnonblank

[Iamnvt]

Hello

I have 2 tables bound by ID:

Table 1:

ID	Name
A	Anna
B	Annie
C	Cud

Table 2:

ID	Group
A	a
A	b
B	b
B	c

How can I make a third table with the first field name value added as follows?

Group	Number
a	Anna
b	Annie
c	Annie

I need to use calculated tables. I tried the following code, but it doesn't work:

ADDCOLUMNS(SUMMARIZE(ADDCOLUMNS(Table2, "1", RELATED(Table1[Name])), Table3[Group])
, "2", FIRSTNONBLANK([1],1))

Thank you

[v-yuta-msft]

@Iamnvt ,

Could you clarify more details about why "Anna" corresponds a, not a and b? And which table is table1, table3 and which column is [1]?

Regards,

Jimmy Tao

[Iamnvt]

@v-yuta-msft : I have editted the post with the correct name.

"Anna" belong to a; because I want to take the first value of it's merge in a, b

[Iamnvt · Accepted solution]

hi,

I found the solution:

EVALUATE
SUMMARIZE (
    ADDCOLUMNS (
        Table2,
        "new name", MINX (
            FILTER ( ALL ( Table2 ), Table2[ID2] = EARLIER ( Table2[ID2] ) ),
            RELATED ( Table1[Name] )
        )
    ),
    Table2[ID2],
    [new name]
)

instead of FIRSTNONBLANK, I use MINX to iterate over Table2, and get the Table[Name] into Table2.

this pattern can be used with CONCATENATEX as well:

EVALUATE
//SUMMARIZE(ADDCOLUMNS(Table2, "new name", MINX(FILTER(All(Table2), Table2[ID2] = EARLIER(Table2[ID2])), RELATED(Table1[Name]))),
//Table2[ID2], [new name])
SUMMARIZE (
    ADDCOLUMNS (
        Table2,
        "new name", CONCATENATEX (
            FILTER ( ALL ( Table2 ), Table2[ID2] = EARLIER ( Table2[ID2] ) ),
            RELATED ( Table1[Name] ),
            ", "
        )
    ),
    Table2[ID2],
    [new name]
)

link to the file here: https://1drv.ms/x/s!Aps8poidQa5zk79LekgUdhxarsSh5A?e=pDN3qI

[amitchandak]

@Iamnvt , Try like summarize(filter(Table1, Table1[Identification name] =max(Table2[Identification group])),Table1[Identification name],Table2[Identification group], "Number",
FIRSTNONBLANK(Table1[Name],""))

[Iamnvt]

@amitchandak it is not working. Summarize can't call out the columns not in the table of Summarize