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.
