Define role-playing dimensions
If you are used to using star schemas, one of the features is the idea of a role-playing dimension. This does not mean that your dimension will be out LARPing somewhere; it means we can use a dimension in multiple ways.
Power BI doesn't directly support role-playing dimensions; instead, it mimics the capability by utilizing active and inactive relationships. A table can only have one active relationship with another table, but it can have multiple inactive relationships. We just tell Power BI when we want to use the inactive relationship instead of the active one.
Date table as a role-playing dimension
In this diagram, our Date
table has two relationships between it and the Sales
table: one on the order date and one on the shipping date. This allows this Date
table to filter the Sales
table by order date or shipping date, or both! It allows the Date
table to pretend to be the "order date dimension" or the "shipping date dimension...