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
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 @v-yiruan-msft  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
Continued Contributor
Continued Contributor

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.

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)

m3tr01d
Continued Contributor
Continued Contributor

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?

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.

m3tr01d
Continued Contributor
Continued Contributor

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
Super User

@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.

It works perfectly in my example.   

This is my example:

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

 

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 @v-yiruan-msft  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!!!!

 

)

 

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.