Forum Discussion
Relationship with Date table
Hi,
So I have an issue where I have to choose to have 1 relationship active due to both of the tables being mapped to date table. Example:
Customer Order (Table) - Created Date is connected to Date table
Invoice/Shipment (Table) - Invoice Date is connected to Date table
Then I would like to connect the Invoice/Shipment Table to Customer Order Table with Order number. Or what is the best practice to solve this? We have such many dates in our data. But usually when we talk about customer orders its about created date and then they become shipment/Invoice - where invoiced date is the primary date.
Some information are exclusive in the different tables which can be intresting to compare to each other, hence i want to connect via order no aswell.
Please let me know if I need to elaborate something.
Kind regards,
Hi RasmusN532 ,
Can you show me a screenshot of your model page in Power BI Desktop? Remember to remove sensitive information.
You can not connect Customer Order (Table), Invoice/Shipment (Table) when they are already connected to Date table respectively, because the model relationships have to follow the star schema instead of a circle.
For the relationship between Customer Order (Table), Invoice/Shipment (Table), consider setting the relationship to be bidirectional. This allows filtering to flow from one table to the other, allowing for a more dynamic analysis of the data.
Since Power BI only allows one active relationship between two tables at any one time, it is possible to create multiple relationships between tables (e.g., Customer Order to Date and Invoice/Shipment to Date), but set the other relationships as inactive. active relationships.
You can then use the USERELATIONSHIP function in a DAX calculation to specify which relationships to use in a particular calculation.
_aa = CALCULATE( SUM('Customer Order'[Amount]), USERELATIONSHIP('Invoice/Shipment'[Invoice Date], 'Date'[Date]) )If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
2 Replies
Hi RasmusN532 ,
Can you show me a screenshot of your model page in Power BI Desktop? Remember to remove sensitive information.
You can not connect Customer Order (Table), Invoice/Shipment (Table) when they are already connected to Date table respectively, because the model relationships have to follow the star schema instead of a circle.
For the relationship between Customer Order (Table), Invoice/Shipment (Table), consider setting the relationship to be bidirectional. This allows filtering to flow from one table to the other, allowing for a more dynamic analysis of the data.
Since Power BI only allows one active relationship between two tables at any one time, it is possible to create multiple relationships between tables (e.g., Customer Order to Date and Invoice/Shipment to Date), but set the other relationships as inactive. active relationships.
You can then use the USERELATIONSHIP function in a DAX calculation to specify which relationships to use in a particular calculation.
_aa = CALCULATE( SUM('Customer Order'[Amount]), USERELATIONSHIP('Invoice/Shipment'[Invoice Date], 'Date'[Date]) )If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hi,
Create an Order Number Dim table and then create a relationship (Many to One and Single) from the 2 tables to the Order Number Table.
