We are going to look at 4 relationship functions in more detail with examples.
I found, there are 4 relationship functions in DAX as per Microsoft documentation.
As the name suggested, these functions are for managing and utilizing relationships between tables.
In this blog, you are going to explore 4 relationship functions with practical examples.
For this case study, I consider the US Superstore dataset from Kaggle.
I have considered the code from one of my case study (How to Create Dimensional Queries and Decomposition Tree).
If you want to get that file, please check the GitHub (US_Superstore_Decomposition Tree project) location.
The document mentions that the CROSSFILTER function is used to implement a specific cross-filtering behaviour in a calculation where a relationship exists between two columns.
When to use the CROSSFILTER function, let us understand with one example.
In the above working file, you have a complete star schema data model.
Now you have one requirement to create one report based on the following criteria.
- Total Sales
- Distinct Product Count for each year.
Obviously, you are going to create two measures, one for total sales based on the sales value of sales fact and another for product count using product id from the product table. Let us implement that.
- Create two calculated measures Total Sales and Product Count.
2. Create a table visual as per the requirement.
3. But from the above report, you can observe, the same product count value is repeated for each year. It means, despite the relationship mentioned between sales fact and product dimension, the filter context is not applied as per the requirement.
4. If you are going to change the relationship between these two tables as both cross filter direction, then all other measures will be impacted. But we want to use both cross filter direction relationship only for this measure. Then how will you do that? Yes, there is an option. We can use the CROSSFILTER function if DAX.
5. Create a measure using the CROSSFILTER function and recreate the report using the new measure. Here is the result.
The USERELATIONSHIP function does not return anything.
It specifies between two columns to define the relationship for a calculation. It uses as an argument for other functions like CALCULATE, CALCULATETABLE etc.
In our current data model, you have two date tables. But if you have one date table and it has one active join with sales table using order date.
Then how to ship date can be used if you have to create total sales based on the ship date.
- Create one date table
- Add an active relationship between the order date of the sales table with the date column of the new date table.
- Keep the inactive relationship between the ship date of the sales table and the date column of the date table.
- Now, if you want to create two measures based on different relationship to the date table, then can use USERELATIONSHIP function with CALCULATE function.
The RELATED function returns a column from another related table.
Here are some key points about this function
- It requires a relationship between the current table and the related table. It follows many to one relationship. Without mentioning a relationship, it will not work.
- This function scans all the values in the specified table irrespective of any filter condition.
- This function can work only in a row context.
- You need to create the measure on the table which is holding much information and returns a single value.
Let us consider one requirement scenario.
- Business wants to compare the yearly sales for all 4 regions as well as non-central regions.
- For the above requirement, you need two measures, one is total sales and another one is sales for the non-central regions.
- Total Sales is already available. Now you create Non-Central Total Sales.
- Then create one sample report to verify whether your measure is really working as expected or not. The report should contain Region, Total Sales and Non-Central Total Sales.
- Now it’s time to create the required report. It should contain Year, Total Sales and Non-Central Total Sales.
The RELATEDTABLE returns a table of values. It considers an existing table as a parameter.
Here are some key points about this function.
- This function is a shortcut function for the CALCULATETABLE function with no logical expression.
- It follows one to many relationships, which means you need to create the measure in that table that is holding one information.
Business wants to see how many times one product has been used in the sales table. That means product count for the sales table.
- Create a calculated column in the product table.
- You need to find out the count of products for the sales table.
Please find the code in the below location
DAX — Chapter 12
In this blog, we learn the 4 relationship functions of DAX with practical examples.
If you have any questions related to this project, please feel free to post your comments.
Please visit my website for other technical resources.
Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.