Introduction to the USERELATIONSHIP function

 As a data analyst, you'll often encounter table relationships that are difficult to perform analysis with. Fortunately, you can alter or manipulate table relationships to facilitate more efficient analysis using the userelationship function. Over the next few minutes, you'll explore the userelationship function, its syntax, and its application. Adventure Works needs to analyze its sales data based on the shipping date. It could create a calculated table for the shipping date and relate it to the sales table. This might work well for a smaller dataset, but Adventure Works has millions of shipping records. A more effective approach is for Adventure Works to use the userelationship function to create a measure that utilizes the inactive relationships between the tables. Before we explore how Adventure Works can analyze its sales data, let's find out more about the userelationship function. The userelationship function is used within the calculate function. It forces the inactive relationship between the tables for the considered calculation to be used. This lets you switch contexts within your data model without changing the default relationship between the tables. It's most useful when there are multiple relationships between two tables. The function allows you to create context aware calculations that can analyze data based on different data dimensions or adjust analysis based on a different category of products. The advantage of userelationship is that it enables you to perform analysis using different relationships available between the related tables without affecting the overall structure of the data model. Now that you've explored how the userelationship function works, let's review the syntax. Begin with the function and then place your argument in parentheses. The argument is the names of the required tables and their respective columns that define the relationship. The order of the columns doesn't matter for the accurate calculation. This function doesn't return a value, but modifies the context of a calculation. This changes the table relationships, meaning that there is no scale or value our table returned as a function is executed. Instead, it changes the context by overriding the relationship between tables. Let's return to Adventure Works data model to explore the syntax in action. As you discovered earlier, Adventure Works data model has a sales fact table and a date dimension table. The data models current active relationship is from the sales table's order date column, and the date table's date column. As no shipping date dimension table exists in the data model, Adventure Works needs to create an additional relationship between the sales fact table and the date dimension table using the sales table's shipping date column. By default, the active relationship for any analysis and visualization is utilized. However, there may be a requirement to calculate the total sales using the shipping date. To do this, it can use the userelationship function within the calculate functions. First, Adventure Works creates a sales by shipping date measure. Then it inputs the calculate function followed by the required argument in parenthesis. In this argument, the sum expression calculates the total of the sales amount column from the sales table. The userelationship function changes the context of this calculation by switching the active relationship from the sales table's order date column and date table's date column to the sales table's shipping and date date to sales shipping date and date, date. When executed, this calculation results in multiple relationships between these tables. An active relationship with the order date and an inactive relationship with the shipping date. This affects only the calculate function where it's used. It won't permanently alter the active relationship. Let's review some important points to remember when working with userelationship. Userelationship only works within the calculate and calculate table functions. If you try to use it elsewhere, you will receive an error. Userelationship functions can be used multiple times within a single calculate function to switch multiple relationships. The userelationship must exist in the data model, but it doesn't have to be active. The userelationship function provides flexibility to derive insights from different perspectives within a data model. This provides a layer of flexibility to Power BI making it an essential function for data analysts to master.