Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Iamnvt
Continued Contributor
Continued Contributor

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

1 ACCEPTED SOLUTION
Iamnvt
Continued Contributor
Continued Contributor

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

 

View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

@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
Continued Contributor
Continued Contributor

@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
Continued Contributor
Continued Contributor

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
Super User
Super User

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

Iamnvt
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.