Skip to main content
Microsoft Idea

Power BI

Needs Votes

Apply Active/Inactive Relationships at the Report, Page, or Visual Level

Vote (4) Share
Simon's profile image

Simon on 14 Aug 2019 23:29:46

Managing models which contain multiple relationships between tables can be troublesome.

An example model may be:
Fact_Invoice[Delivery Date] -> Dim_Date[Date]
Fact_Invoice[Purchase Date] -> Dim_Date[Date]

There are two solutions in situation: (a) implement role-playing dimensions; or (b) leverage USERELATIONSHIP () at the measure level.

As a Report Developer, I cannot setup option (a). However, using live connection, I can implement option (b) which results in a bloated the model containing many measures.

This Power BI idea proposes the following happy medium: allow Report Developers, using Power BI Desktop, to define which relationships are active or inactive at the visual or report level.

For example, I select my pie chart, click "Define Relationships", in the resulting GUI I then define what relationships to activate or deactivate and then hit apply.

Behind the scenes, this would effectively be the application of USERELATIONSHIP () as a "filter context".

Comments (1)
Simon's profile image Profile Picture

Vadim Panfilov on 12 Aug 2021 14:16:43

RE: Apply Active/Inactive Relationships at the Report, Page, or Visual Level

We often have data models with circular relationships, and there is no way to specify which one will be active for a chart.

To overcome this limitation, we often clone the table or use merge query; neither of them are ideal solutions.