Overview of Role-Playing Dimension

You may define a role-playing dimension as a dimension used to filter related facts differently. Moreover, in a data warehouse, the accepted design approach is used to define a single date dimension table. Moreover, at query time, the “role” of the date dimension is being established by the fact column you use to join the tables.

We may imitate the design by creating multiple relationships between two tables, in a Power BI model. Moreover, while this design is possible to achieve, it’s equally essential to understand that there can only be one active relationship between two Power BI model tables. Further, all other relationships must be set to inactive mode.

However, the only way to use an inactive relationship is to define a DAX expression that uses the USERELATIONSHIP function. But, this work can be tedious, especially when the reseller table defines many measures. It also creates Fields pane clutter, with an overabundance of measures.

Some of the limitations in a role-playing dimension

• Firstly, when report authors are dependent on summarizing columns, rather than defining measures, they can’t achieve summarization for the inactive relationships without writing a report-level measure.
• Secondly, report-level measures can only be defined when authoring reports in Power BI Desktop.
• Thirdly, with only one active relationship path between date and reseller sales, it is not suggested to simultaneously filter reseller sales by different types of dates.

For overcoming these shortcomings, one of the most commonly used Power BI modeling technique is to create a dimension-type table for each role-playing instance. In general, you can create additional dimension tables as calculated tables, using DAX. By using calculated tables, the model can contain a Date table, a Ship Date table and a Delivery Date table, each with a single and active relationship to their respective reseller sales table columns.

However, the design approach doesn’t require you to define multiple measures for different date roles, plus it permits simultaneous filtering by different date roles. However, the use of design approach leads to duplication of the date dimension table resulting in an increased model storage size. In general, dimension-type tables store fewer rows relative to fact-type tables.

Best design practices when creating model dimension-type tables for each role

• Firstly, ensure that the column names are self-describing.
• Secondly, when required, ensure table descriptions provide feedback to report authors (through Fields pane tooltips) about how filter propagation is configured.
• Further, it is important when the model contains a generically named table, like Date, which is used to filter many fact-type tables.