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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors