Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I am trying to summarize/groupby a Unioned table.
I use a combination of UNION(SUMMARIZECOLUMNS(...),SUMMARIZECOLUMS(...)) and would like to summarize further this table so that I only have one unique row per category (Name).
Summarize table 1:
SUMMARIZECOLUMNS(
TBL1.NAME
,"AMOUNT VALUES1"
,SUM(Value1)
,"AMOUNT VALUES2"
,0
)
NAME | AMOUNT VALUES1 | AMOUNT VALUES2 |
Name 1 | 5 | 0 |
Name 2 | 3 | 0 |
Summarize table 2:
SUMMARIZECOLUMNS(
TBL2.NAME
,"AMOUNT VALUES1"
,0
,"AMOUNT VALUES2"
,SUM(Value2)
)
NAME | AMOUNT VALUES1 | AMOUNT VALUES2 |
Name 1 | 0 | 2 |
Name 2 | 0 | 2 |
Name 3 | 0 | 2 |
The Union Table:
UNION(
SUMMARIZECOLUMNS(
TBL1.NAME
,"AMOUNT VALUES1"
,SUM(Value1)
,"AMOUNT VALUES2"
,0
)
,
SUMMARIZECOLUMNS(
TBL2.NAME
,"AMOUNT VALUES1"
,0
,"AMOUNT VALUES2"
,SUM(Value2)
)
)
NAME | AMOUNT VALUES 1 | AMOUNT VALUES 2 |
Name 1 | 5 | 0 |
Name 2 | 3 | 0 |
Name 1 | 0 | 2 |
Name 2 | 0 | 2 |
Name 3 | 0 | 2 |
The problem:
I want the table above to be summarized further in this manner:
NAME | AMOUNT VALUES 1 | AMOUNT VALUES 2 |
Name 1 | 5 | 2 |
Name 2 | 3 | 2 |
Name 3 | 2 |
But When I try to include a GROUPBY or SUMMARIZECOLUMN on top of the union I am not able to sum the "AMOUNT VALUES" as it is not detected.
This is what I tried
SUMMARIZECOLUMNS(
UNIONTABLE
,NAME --> it gets picked up OK
,"AMOUNT1"
,SUM( I cannot chose the "AMOUNT VALUES1" created earlier)
Any thought on how to get it to work?
Regards,
Alex
Solved! Go to Solution.
Hi @Alex_pg,
Indeed, on my PC the engine's behaviour is the same.
But you can solve your task in a different way without UNION:
[DAX] code in plain text:
Res =
ADDCOLUMNS (
DISTINCT ( UNION ( VALUES ( TBL1[Name] ), VALUES( TBL2[Name]) ) ),
"Amt1",
VAR CurrentName = [Name]
RETURN SUMX ( FILTER ( TBL1, [Name] = CurrentName ), [Value1] ),
"Amt2",
VAR CurrentName = [Name]
RETURN SUMX ( FILTER ( TBL2, [Name] = CurrentName ), [Value2] ) )
Best Regards,
Alexander
Hi @Alex_pg,
Indeed, on my PC the engine's behaviour is the same.
But you can solve your task in a different way without UNION:
[DAX] code in plain text:
Res =
ADDCOLUMNS (
DISTINCT ( UNION ( VALUES ( TBL1[Name] ), VALUES( TBL2[Name]) ) ),
"Amt1",
VAR CurrentName = [Name]
RETURN SUMX ( FILTER ( TBL1, [Name] = CurrentName ), [Value1] ),
"Amt2",
VAR CurrentName = [Name]
RETURN SUMX ( FILTER ( TBL2, [Name] = CurrentName ), [Value2] ) )
Best Regards,
Alexander
I completely forgot to reply.
Thank you very much, it worked perfectly.
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |