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
Alex_pg
Advocate I
Advocate I

Union 2 tables then Groupby with calculation on union

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
)

NAMEAMOUNT VALUES1AMOUNT VALUES2
Name 1  50
Name 2   30

 

Summarize table 2:

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

   ,0
   ,"AMOUNT VALUES2"
   ,SUM(Value2)
)

NAMEAMOUNT VALUES1AMOUNT VALUES2
Name 1   02
Name 2   02
Name 3   02

 

The Union Table:

UNION(

SUMMARIZECOLUMNS(

   TBL1.NAME
   ,"AMOUNT VALUES1"
   ,SUM(Value1)

   ,"AMOUNT VALUES2"

   ,0
)

,

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

   ,0
   ,"AMOUNT VALUES2"
   ,SUM(Value2)
)

)

 

NAMEAMOUNT VALUES 1AMOUNT VALUES 2
Name 1   50
Name 2   30
Name 1   02
Name 2   02
Name 3   02

 

 

The problem:
I want the table above to be summarized further in this manner:

NAMEAMOUNT VALUES 1AMOUNT VALUES 2
Name 1   52
Name 2   32
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

 

1 ACCEPTED SOLUTION
barritown
Super User
Super User

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:

barritown_0-1685608339338.png

[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

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

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:

barritown_0-1685608339338.png

[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

My YouTube vlog in English

My YouTube vlog in Russian

I completely forgot to reply.
Thank you very much, it worked perfectly.

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.