dax (userelationship) to enable dimension based on validity of dimensionid

[cturner]

Ok, here's my problem.  I can't post a dataset because it's proprietary and directquery.

I have measures in fact.one that need to be joined to benchmarks in fact.two.  I have 3 (ok, more like 6) dimensions that link the two.

In some cases, one or more of the dimensionid's are null in the benchmark fact.  This is as supplied, and out of my immediate control.  In that case, I need the null to be treated like an ALL, and have the benchmark indicated by the existing dimensions apply to all the measures covered by the null dimension.

So my output matrix looks (roughly) like this

Rows:

Dimension 1

Dimension 2

Dimension 3

Values:

Measure

Benchmark

I think what I need to do is disable relationships between the tables and use USERELATIONSHIP to enable them only when the dimensionid is not null.   I'm hazy on the logic and syntax, however.  Doing some reading on the subject now, but figured I'd put this out for comment as is.   Let me know if I need to provide further detail.

[TheOckieMofo]

the USERELATIONSHIP function is intended to call a relationship that is already there, but it is inactive. When you have more than one relationship between two tables, one must be the "active" (read default) relationship. The other is the "inactive" relationship. The USERELATIONSHIP function calls that inactive relationship. So i'm not sure if that is what you're going for here. You can control which relationships are active vs. inactive by going to the manage relationships area of Power BI and toggling the check mark for active on and off to suit your needs.

It is possible to create "virtual" relationships as described in this article: Virtual Relationships

That article is by the Italians, Russo and Ferrari, so it's pretty high level, but perhaps that will help.