Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
6 years ago
Solved

Table calculated with firstnonblank

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 ...
Need Help
  • Iamnvt's avatar
    Iamnvt
    6 years ago

    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

     

v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

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's avatar
Iamnvt
Continued Contributor
6 years ago

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's avatar
    Iamnvt
    Continued Contributor
    6 years ago

    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