DAX UNION does not work as expected

[liudmila]

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?

[m3tr01d]

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]

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]

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]

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]

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]

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]

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

[liudmila]

It works perfectly in my example.   

This is my example:

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

[Anonymous]

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

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

[liudmila · Accepted solution]

Hi @Anonymous  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!!!!

)