Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |