Forum Discussion

liudmila's avatar
liudmila
Frequent Visitor
5 years ago
Solved

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?

 

 

Need Help
  • liudmila's avatar
    liudmila
    5 years ago

    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!!!!

     

    )

     

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    liudmila
    Frequent Visitor
    5 years ago
    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
  • m3tr01d's avatar
    m3tr01d
    Continued Contributor
    5 years ago

    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's avatar
      liudmila
      Frequent Visitor
      5 years ago

      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's avatar
        m3tr01d
        Continued Contributor
        5 years ago

        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?