{"id":3852,"date":"2021-12-21T22:01:27","date_gmt":"2021-12-22T06:01:27","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3852"},"modified":"2021-12-21T22:01:29","modified_gmt":"2021-12-22T06:01:29","slug":"how-to-use-a-non-active-relationship-in-power-bi-userelationship","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3852","title":{"rendered":"How to Use a Non-active Relationship in Power BI – USERELATIONSHIP"},"content":{"rendered":"\n

Another DAX function that allows you to override the default behavior is USERELATIONSHIP.<\/p>\n\n\n\n

Consider the following data model example.<\/p>\n\n\n\n

\"Screenshot<\/a><\/figure>\n\n\n\n

The preceding screenshot shows an established relationship between the Date and OrderDate<\/strong> columns, as shown by the highlighted line connecting the two. The solid line between the two tables indicates that it is the active relationship, meaning that by default, any slicing on the date table where measures in the Sales data are being displayed will be along the OrderDate<\/strong> column. A dashed relationship exists between the Date<\/strong> and ShipDate<\/strong> columns, indicating that it is an inactive relationship. This relationship will never be used unless explicitly declared in a measure.<\/p>\n\n\n\n

The goal is to build the following report, where you have two visuals: Sales by Ship Date<\/strong> and Sales by Order Date<\/strong>.<\/p>\n\n\n\n

\"Screenshot<\/a><\/figure>\n\n\n\n

These visuals show the sales over time, but the first visual is by order date and the second is by ship date so, though they are both dates, a different data point is associated with them to get both sets of data on the same visual.<\/p>\n\n\n\n

To create this measure for Sales by Ship Date, you can use the DAX function USERELATIONSHIP(). This function is used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships. It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression, as shown in the following example:<\/p>\n\n\n\n

Sales by Ship Date = CALCULATE(Sum(Sales[TotalPrice]), USERELATIONSHIP(Sales[ShipDate],'Calendar'[Date]))<\/code><\/p>\n\n\n\n

Now, you will be able to create the second visual.<\/p>\n\n\n\n

Ref: Use relationships effectively – Learn | Microsoft Docs<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Another DAX function that allows you to override the default behavior is USERELATIONSHIP. Consider the following data model example. The preceding screenshot shows an established relationship between the Date and OrderDate columns, as shown by the highlighted line connecting the two. The solid line between the two tables indicates that it is the active relationship, meaning that by default, Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1005,1006,1004],"class_list":["post-3852","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-power-bi-userelationship","tag-use-a-non-active-relationship-in-power-bi","tag-userelationship"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t