![]() ![]() ![]() Now you can see that we get 12 rows, however no single column gives the result that we need. So let’s first crossjoin the two tables and see the results. However, there is an operator in DAX which generally generates more rows than its source tables – CROSSJOIN (except when any one of the participating tables has only one row). I will be using DAX Studio for writing my queries and displaying the results (though you might as well as use this in SSMS or in the DMX query editor for SSRS depending on your purpose).Ģ) The first thing to understand here is that DAX, as a query language, can not add rows usually, and UNION requires that we get more rows (since it is the combined result of both the tables). The result should be 7 rows as shown belowįollow the steps below for the solution:-ġ) Import the two tables to PowerPivot (you can also use this technique on a SSAS tabular model). There are just two columns in the tables – country and state and we need to do an UNION operation on these two tables. Well, since difficult is such a subjective word, I decided to jot down the technique, maybe it might seem easy for you guys!įor the purpose of this demonstration, I am using two tables having identical structures. A particularly eye-catching thing in my post was the use of Microsoft Query instead of DAX Query to obtain the required reverse-linked table and one of the reasons I gave was that it was difficult to do an UNION operation using DAX queries (yes, you heard it right. In one of my previous posts – Row Selection Using Slicers in PowerPivot – Part 1, I had demonstrated the use of what Marco Russo defined as Reverse Linked Tables and Linkback tables. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |