Overview
One of the primary requirements of a query is to join different tables to create the desired resultset. There are a couple of ways to achieve this in DAX by using the Add Columns function as well as the Summarize function. In this chapter, we will learn how to use both of these functions and join data from different tables.
Explanation
Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. All the perspectives that we saw in the last chapter, will be listed in the Cube pane. Select the Internet Operation perspective in the Cube pane, and type the below expression.
evaluate (
ADDCOLUMNS( 'Customer', "Customer City", RELATED ('Geography'[City] ) )
)
Execute the above mentioned expression and you should be able to find the results as shown below. In this expression, we are trying to use the add columns function and retrieve the fields from the Customer table and the corresponding City field from the Geography table. If you carefully study the relationship between Customer and Geography table in SSDT, you would be able to find that there exists a relationship between these two tables. The same relationship is accessed by the RELATED function and corresponding values are fetched for each record in the Customer table.
Let’s say we intend to query certain specific fields from the Customer table and join them with the City field from the Geography table. We can achieve this by using the SUMMARIZE function too that we saw in the last chapter. Try to execute the below expression and analyze the results. In the below expression, the formula inherently uses the relationship between the two tables and joins them using a left join. This means that all the records in the Customer table will be fetched and the corresponding records from the Geography table will be fetched and joined. Wherever the corresponding record from Geography is not found, the field will have blank values in the resulting recordset.
evaluate ( SUMMARIZE( 'Customer', [First Name], [Last Name], 'Geography'[City] ) )
In this way we can join the data from different tables using the above mentioned functions. There are a number of other functions like UNION, INTERSECT, EXCEPT, NATURALINNERJOIN, NATURALLEFTOUTERJOIN and others. You can read more about these functions here.
Additional Information
- Now that you have learned about selecting and joining data, try developing complex queries that join multiple tables using a complex selection criteria and aggregates the results using complex statistical functions. Practicing such queries will help to increase command and familiarity with DAX functions.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
