Welcome to Excel Jet Consult blog post. In this tutorial we want to learn how to use the USERELATIONSHIP Dax Function. Let’s get started
USERELATIONSHIP Function specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and column Name2. Note, the relationship can be deployed with functions that take filter as an argument. So, of these functions are CALCULATE, CALCULATETABLE and Time Intelligence functions
USERELATIONSHIP Syntax =USERELATIONSHIP(columnName1, columnName2)
In the caption below, we have loaded our fact table and dimension tables to the data model.
Create Date Table
In the Diagram view of caption below, we have Transaction_facttbl which is the fact table and Dim_Product and Dim_Account_Manager which are the two Dimension tables. We have established one-to-many relationships between these tables.
The next thing we need to do is create Calendar Date Table. To do that
- In the Calendars group of the Design tab, click on Date Table dropdown and select New
- Drag and connect Order Date from the Transaction_facttbl to Date column of the Calendar Date Table
- Drag and connect Ship Date from the Transaction_facttbl to Date column of the Calendar Date Table
In the caption above, we can see that we have one tick connecting line governing Order Date and Date which is one-to-many relationships. This relationship is active. On the other hand, we also have dotted connecting line between Ship Date and Date. The dotted connecting line indicate that, although, there is a one-to-many relationship between the tables, the relationship is inactive.
Create DAX Measures
The next thing we want to do is to create DAX measures.
- From the Diagram view, click on Home and Data view
- We have already created two DAX measures which are Total Amount & Total Units
- In the formula bar of the Transaction_facttbl sheet tab, execute the DAX formula:
Total Amount by Ship Date:=CALCULATE(Transaction_facttbl[Total Amount],USERELATIONSHIP(Transaction_facttbl[Ship Date],’Calendar'[Date]))
And
Total Units by Ship Date:=CALCULATE(Transaction_facttbl[Total Units],USERELATIONSHIP(Transaction_facttbl[Ship Date],’Calendar'[Date]))
So, we have two DAX Measures which are Total Amount by Ship Date and Total Units by Ship Date. See the caption below
Create Pivot Table Report
Next, we want to create Pivot Table Report. To do that:
- In the Home tab, click on PivotTable and choose the sheet to drop the PivotTable
- In the PivotTable Fields, drag and drop Month from the Calendar into the Rows area
- Drag and drop Total Amount & Total Units DAX measures into the Values area respectively
- Drag and drop Total Amount by Ship Date & Total Units by Ship Date DAX measures into the Values area respectively
In the above caption, we note the following:
In the Month of January, Total Amount and Total Units are ₦116,804.76 and 2,007 respectively. However, the Total Amount by Ship Date and Total Units by Ship Date are ₦119,894.88 and 1,960 respectively.
This implies that, for one reason or another, we received more payment by ship date compared to what we received by Order Date.