locked
using the union function in dax RRS feed

  • Question

  • Hi we run 2012 enterprise. Just getting my head around the dax language a little bit. I thought this would be valid after experimenting with the summarize function but dax doesn't like it. I'm guessing it never will but can someone explain why.  My long term goal is to "union" some materialized data with some manufactured data in tabular.  By manufactured I mean data that my be too expensive volume wise to store but perhaps very quick to generate from some "joins" on the core tables we used to build the cube in the first place.  This was a baby step in that direction.  And was meant to union the same data with itself before trying to learn more about manufacturing data.

    evaluate 
    (
    
    union(
    summarize 
    		             ( 'Cube_Fact',    
    					   'Cube_Dim1'[Dim1Name],
    					   "Heading",sum('Cube_Fact'[Cnt])
    					 ),
    summarize 
    		             ( 'Cube_Fact',    
    					   'Cube_Dim1'[Dim1Name],
    					   "Heading",sum('Cube_Fact'[Cnt])
    					 )
    )
    
    )


    • Edited by db042190 Tuesday, July 25, 2017 5:15 PM clarity
    Tuesday, July 25, 2017 5:08 PM

Answers

  • ...maybe I need 2016.  If so, how did folks union before 2016?

    Correct, you need 2016. Before that it was simply not possible in DAX.

    There are some hacky work arounds. You could setup a linked server and use T-SQL to union 2 DAX queries using OPENQUERY() calls. Or of you are using SSRS you can just use 2 queries and position that tables so that the bottom one is almost touching the top one and if their columns are the same sizes it will look like a single table...


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 25, 2017 8:30 PM
  • Hi db042190,

    Thanks for your question.

    Yes, Union function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only.

    To union two result in SSAS 2012, you can also refer to below blog:
    UNION Operation in DAX Queries 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by db042190 Wednesday, July 26, 2017 11:13 AM
    Wednesday, July 26, 2017 2:21 AM

All replies

  • ...maybe I need 2016.  If so, how did folks union before 2016?
    Tuesday, July 25, 2017 5:21 PM
  • ...maybe I need 2016.  If so, how did folks union before 2016?

    Correct, you need 2016. Before that it was simply not possible in DAX.

    There are some hacky work arounds. You could setup a linked server and use T-SQL to union 2 DAX queries using OPENQUERY() calls. Or of you are using SSRS you can just use 2 queries and position that tables so that the bottom one is almost touching the top one and if their columns are the same sizes it will look like a single table...


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 25, 2017 8:30 PM
  • Hi db042190,

    Thanks for your question.

    Yes, Union function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only.

    To union two result in SSAS 2012, you can also refer to below blog:
    UNION Operation in DAX Queries 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by db042190 Wednesday, July 26, 2017 11:13 AM
    Wednesday, July 26, 2017 2:21 AM