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:

 

IDName
AAnna
BAnnie
CCud

 

Table 2:

 

IDGroup
Aa
Ab
Bb
Bc

 

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

 

GroupNumber
aAnna
bAnnie
cAnnie

 

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

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

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

    • Iamnvt's avatar
      Iamnvt
      Continued Contributor
      6 years ago

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

  • 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