Power BI: Handling Multiple Date Keys

Scenario

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 three dates at the same time.

Solution: 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. You create active relationships between the sales table and each date table.

Does this meet the goal?

No, this solution does not meet the goal. In Power BI, you can only have one active relationship between two tables at a time. Creating active relationships between the sales table and each of the three date tables simultaneously is not possible.

Instead, you should create one active relationship and two inactive relationships. Then, you can use DAX functions like USERELATIONSHIP to activate the inactive relationships as needed in your calculations. Here's a brief outline of the steps:

1. Import the Date Table and Sales Table from your Azure SQL database.

2. Create Relationships:

• Create an active relationship between the Sales table and the Date table using one of the date keys (e.g., Due Date).

• Create inactive relationships between the Sales table and the Date table using the other date keys (e.g., Order Date and Delivery Date).

1. Use DAX to Activate Relationships: • Use the USERELATIONSHIP function in your DAX measures to switch between the inactive relationships when needed.

This approach allows you to analyze sales over time based on all three dates without renaming tables or creating calculated tables unnecessarily.