cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
liudmila
Frequent Visitor

DAX UNION does not work as expected

Hi,

I need to create a table ,every row comes from different sources.

first row from cube1, second row from cube2 etc.

I created a custom table using UNION

Customers=UNION(table1,table2,table3,table4)

table1,2,3 were created as a custom tables using SUMMARIZE()

for example :

table1 = SUMMARIZE('Sales',column1, column2,sum(totalvalue)) - 'Sales' is a real cube

table 4 was created similer using SUMMARIZE BUT as a sourse table , I used a custom table from 2 joined tables.

I tried 2 ways to join the tables: 1) merge query, 2) dax naturalleftouterjoin

Result table 4 is not shown. No errors

 

Any ideas?

 

 

1 ACCEPTED SOLUTION

Hi @yingyinr  thank you very much  for your suggection.  It was sorted. UNION did not work for me so I found the example in internet and it solved my problem

instead of using just Union (table1, table2,table3, table4) 

I used Union(table21, table2,table3, SELECTCOLUMNS ( Table4, "Column1", Table4[Column1], "Column2", Table4[Column2], "Column3", Table4[Column3] ) and it works!!!!

 

)

 

View solution in original post

10 REPLIES 10
m3tr01d
Resolver I
Resolver I

Hi @liudmila ,

just out of curiosity, how many tables are "Calculated tables" in your model?

I would be interested to know why did you need to summarize data in a calculated table.

liudmila
Frequent Visitor

All of them are calculated tables:   table1,table2 table3, table4 

I use SUMMARIZE because I need to get sum of totalamount group by date, state etc. I found some examples how to to that using SUMMARIZE.   So I created a new table with  Date, State, SUM of total amount and then i can play with this table. (This table is not for visualization but for the futher manipulations)

Ok, I'm just asking because I've been doing some DAX for 5 years now and I can say that every person that I've seen used Summarize inside Calculated table didn't need to use it and it just took additional unnecessary memory inside their model. I'm not saying you are not using it properly, I'm just questioning the idea behind it. 

Can you share with us a simple pbix file with data and visual you want to create?

liudmila
Frequent Visitor

Thank you for your suggestion, I don't know how i can avoid this step? Definitely  I'd like to make the model using the best options but  in internet I cannot find anything better.

 

I will create a simple it will take time 🙂  Thank you.

Exactly, of course, we can answer your original question related to Union but this won't solve the future issues you'll encounter if your Data model is not designed with best practices 🙂

liudmila
Frequent Visitor

2 tables were joined by using DAX NATURALLEFTOUTERJOIN
 
table4 =
 
VAR A =
SELECTCOLUMNS (
Sales,
"Month_Year", Sales[Processed date.Month] & Sales[Processed date.Year],
"ProcessedAt" , Sales[Processed date.DateValue],
"Region", Sales[Site.Region]
 
)
VAR B =
SELECTCOLUMNS (
Forecast,
"Month_Year", Forecast[Processed date.Month] & Forecast[Processed date.Year],
"AU OOH", Forecast[AU OOH],
"UK OOH",Forecast[UK OOH]
)
var Result = NATURALLEFTOUTERJOIN ( A, B )
return Result
amitchandak
Super User IV
Super User IV

@liudmila , can share how you created table4. if possible some sample data to create the same.

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

It works perfectly in my example.   

This is my example:

https://drive.google.com/file/d/1TAVWiVqS22K16hPGvk-o86aY-JrvUp6k/view?usp=sharing

 

yingyinr
Community Support
Community Support

Hi @liudmila ,

Please update the formula of calculated table AllDays_Tax as below:

AllDays_Tax = 
VAR A =
    SELECTCOLUMNS (
        Flowers,
        "Month_Year", Flowers[Date].[Month] & Flowers[Date].[Year],
        "Date", Flowers[Date],
        "Value", BLANK ()
    )
VAR B =
    SELECTCOLUMNS (
        Tax,
        "Month_Year", Tax[Date].[Month] & Tax[Date].[Year],
        "Date", BLANK (),
        "Value", Tax[Value]
    )
VAR Result =
    DISTINCT ( UNION ( FILTER ( A, NOT ( ISBLANK ( [Date] ) ) ), B ) )
RETURN
    Result

yingyinr_2-1623922645552.png

If the above one is not working, could you please tell me what's your final expected result with the backend logic and function? I want to check whether we can achieve it only by creating measures or calculated columns... Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @yingyinr  thank you very much  for your suggection.  It was sorted. UNION did not work for me so I found the example in internet and it solved my problem

instead of using just Union (table1, table2,table3, table4) 

I used Union(table21, table2,table3, SELECTCOLUMNS ( Table4, "Column1", Table4[Column1], "Column2", Table4[Column2], "Column3", Table4[Column3] ) and it works!!!!

 

)

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors