Forum Discussion
relationship active & inactive
Hey, I am preparing the exam and I think the question is a bit ambiguous. My answer is No, No, No, No, Yes.
Need help to confirm. 😅
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following
date foreign keys:
Due Date
Order Date
Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution1: For each date foreign key, you add inactive relationships between the sales table and the date table
Solution2: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and Delivery Date.
Solution3: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.
Solution4: You create measures that use the USERELATIONSHIP DAX function to lter sales on the active relationship between the sales table and the date table.
Solution5: You create measures that use the USERELATIONSHIP DAX function to lter sales on the inactive relationship between the sales table and the date table.
Which solutions will work?
Hi New_worker - Please find the answers in Bold
Solution1: For each date foreign key, you add inactive relationships between the sales table and the date table YES
Solution2: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and Delivery Date. YES
Solution3: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.NO
Solution4: You create measures that use the USERELATIONSHIP DAX function to lter sales on the active relationship between the sales table and the date table. NO
Solution5: You create measures that use the USERELATIONSHIP DAX function to lter sales on the inactive relationship between the sales table and the date table. YES
I hope it helps
6 Replies
Hi New_worker - Please find the answers in Bold
Solution1: For each date foreign key, you add inactive relationships between the sales table and the date table YES
Solution2: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and Delivery Date. YES
Solution3: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.NO
Solution4: You create measures that use the USERELATIONSHIP DAX function to lter sales on the active relationship between the sales table and the date table. NO
Solution5: You create measures that use the USERELATIONSHIP DAX function to lter sales on the inactive relationship between the sales table and the date table. YES
I hope it helps
Hi rajendraongole1 ,
Thanks for your reply. Could you give more explaination on solution 1 &2? Why will they work?
Solution1, the inactive relationship should not work for filter, right..
Solution2, it seems work, but I think it should create revelant relationships between Order and Sales, Delivery and Sales...
I am confused with the question..
Sol1: YES
You can create inactive relationships for each date key, and then activate them on-demand using DAX with USERRELATIONSHIP. This is a valid approach.
Sol2: YES
By duplicating (referencing) the date table in Power Query, you create separate tables for Due Date, Order Date, and Delivery Date. This will allow you to build individual relationships for each date, which is a valid approach.
Hope the above explaination clears
Hi New_worker ,
Hi! I’m glad to see that your issue seems to be resolved. If the provided solution helped, could you kindly mark it as the accepted solution? This helps other users with similar questions find the answer more easily. Thank you!
