DAX UseRelationship Function

In DAX, a major design approach change has to do with role-playing dimensions. They don’t need to be built into your warehouse differently, but the way they are handled in creating formulas and browsing is much different. Using the date dimension (as it’s the most common role playing dimension) as an example, in Multidimensional role-playing dimensions are materialized during cube processing and the dimension is propagated out for each date in your fact table. Therefore, using OrderDate, ShipDate, and DueDate, when a multidimensional cube is processed, each of these dates become their own dimension within the cube. In tabular, no such physically materialized dimensions exist, and only 1 date dimension is created and is used. Logically, this makes sense if we think about how efficient tabular is at storing only distinct values with pointers to retrieve the current record set, but it is worth exploring what the trade offs are.

 

When a business user has a question about one of these dates, they are asking the same question, just the way we answer it is different. For example, take the below approach in multidimensional to answer the question of how many orders did I receive by day/month/year. We would simply take our materialized Order Date dimension, drag over our hierarchy, and expand it appropriately. The result would look like the below.

 image

 

The real question we’re asking here is “How many total orders did I have?” Date is merely an attribute of that fact, just as it in a table. Do we really need OrderDate as it’s own explicit dimension? Even more relevant, we’re limiting ourselves in this view in that we can only look at Orders by date right now, if we add number of shipments, for example, we’d be looking at number of shipments by order date, which doesn’t tell us what we’re really looking for. The calculation where we specify we want Orders is in our calculation, so we don’t need an explicit order date dimension.

 

Creating the same thing in DAX is much simpler. Creating a PowerPivot workbook, and bringing in the FactInternetSales table along with the date dimension, we’ve created the relationship from the fact table for OrderDate, ShipDate, and DueDate to our DateKey in the date dimension. I’ve also specified that ShipDate is the active relationship. Our table relationship in the PowerPivot diagram view looks like this:

image

 

The questions we want to answer is “Number of Orders” and given the above limitation in multidimensional, “Number of Shipments.” In DAX, we can use the function CALCULATE combined with USERELATIONSHIP to explicitly specify what relationship we want DAX to use when performing that calculaton. Bringing up the FactInternetSales in data view, navigate to the calculations section of the table and under Orderdate you want to create a function using both CALCULATE and USERELATIONSHIP.

 

NumberOfOrders:=CALCULATE(COUNT(‘FactInternetSales'[OrderDate]),USERELATIONSHIP(‘FactInternetSales'[OrderDateKey],’DimDate'[DateKey]))

 

The Userelationship function is particularly handy, as we can tell DAX to not use the default relationship and to use the relationship we specify instead. It only accepts two arguments: the table we are specify the relationship from (‘FactInternetSales’[OrderDateKey]) and the table we are specifying the relationship to (‘DimDate’[DateKey]).

 

Now under the ShipDateKey column in the fact table we’re going to do the same thing, just specifying shipments instead of orders:

 

NumberOfShipments:=CALCULATE(COUNT(‘FactInternetSales'[ShipDateKey]),USERELATIONSHIP(‘FactInternetSales'[ShipDateKey],’DimDate'[DateKey]))

Now create a pivot table, drag over a date hierarchy (or just the year/month attributes if you haven’t built a calendar hierarchy), and then drag over your new measures. The result should look like:

 

image

 

Coincidentally, since we’re using AdventureWorks both NumberOfOrders and NumberOfShipments have the same final grand total, but you can see how the number of orders and number of shipments changed by date. And since we’re using just the straight dimension (not a materialized Order Date dimension) we can also rest easy knowing that the business is getting the correct values when they just drag over date. You also get the added benefit that business users only have 1 date that they can select, so you won’t get the “Why are there so many?” question.

Advertisements

One thought on “DAX UseRelationship Function”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s