Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
@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
@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
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
@Iamnvt , Try like summarize(filter(Table1, Table1[Identification name] =max(Table2[Identification group])),Table1[Identification name],Table2[Identification group], "Number",
FIRSTNONBLANK(Table1[Name],""))
@amitchandak it is not working. Summarize can't call out the columns not in the table of Summarize
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |