Thanks in advance for taking time and reading this
We have a source system with several tables, and many relationships. Not all of the relationships can be “enabled” within the tabular model at once, because they would create ambiguous joins, however, all the relationships are valid (and represent real relationships in the source SQL server system, which works fine as an operational database for our user community).
We want to create reports from the tabular model, in Power BI, which we’ve been doing as standard practice for a while.
The issue is that for Power BI Report A, we want a certain set of relationships to be enabled, and for Power BI Report B, we want other relationships to be enabled.
We have investigated “perspectives”, but this just seems to allow showing/hiding entities, but doesn’t allow specific relationships to be enabled in one perspective, and disabled in another, so that doesn’t seem to help.
Within Power BI (because it uses the Tabular model), there is no way we are aware of to enable/disable relationships. This is set within the tabular model.
We definitely want to avoid having multiple tabular models, because this defeats the object of having a single source of the truth, which leverages entities from different parts of the organisation (and we have a development practice where we develop against one tabular model, before then deploying it to live etc, and having multiple models just seems unnecessarily complex.
We are aware of the DAX “USERELATIONSHIP” feature, which can be used in “Calculatetable”, and we don’t want to resort to this, slightly artificial, step of having to create a table where the underlying data is already there, and , were it not for the enabling/disabling issue, all the relationships are already in place.
If anyone has any helpful thoughts, that would be much appreciated.
Note-
I understand about the UERELATIONSHIP thing, but we don’t want to create measures, we just want to use existing fields from multiple tables, which are respecting the relevant set of relationships.
