topic Userelationship doen't works many to many in DAX Commands and Tips

Userelationship doen't works many to many

Rift — Tue, 01 Mar 2022 20:03:00 GMT

Hi.

I'm trying to do a Userelationship with many to many imactive rrlation, but doesn't works. If I disable my main relation (1,*) and active my secondary and desirable relation (*,*) manually, the numbers works perfectly. But when I do it with the measure: measure = CALCULATE( SUM(column A), USERELATIONSHIP( inactiveRelation-key-from-table-A, inactiveRelation-key-from-trable-B)), it doesn't works.

The result of measure doesn't change, like the main relation was active.

Is possible to do a USERELATIONSHIP with a many to many relation?

Re: Userelationship doen't works many to many

Anonymous — Wed, 02 Mar 2022 08:31:27 GMT

Hi Rift , It is recommended to avoid Many to Many relationships. The USERELATIONSHIP function works best with One to One or One to Many relationship. You could consider changing your Data Model avoid the current scenario.

However, there is an alternative approach using the TREATAS function. Please consider the following for background on the relationships vs treatas - Relationships in Power BI and Tabular models - SQLBI

Re: Userelationship doen't works many to many

BeaBF — Wed, 02 Mar 2022 13:07:55 GMT

Rift The best approach is to create a fact table, which is a table with the column you use to relate the two tables. This way you would have a one to many connection for each of the two tables.

Here is a very simple example, but if you want to share your data I can help you in modeling an effective scheme.

In this way you can combine in a single table the fields relating to the transactions both of the first table (for example amount) and of the second one (for example City).

Re: Userelationship doen't works many to many

Rift — Wed, 02 Mar 2022 23:46:17 GMT

First of all, thanks you for your responses.

I solved my problem, disabling main relationship:

CALCULATE(CALCULATE( SUM(column A), USERELATIONSHIP( inactiveRelation-key-from-table-A, inactiveRelation-key-from-trable-B)), CROSSFILTER(currentActiveRelationship-key-from-table-A, currentActiveRelationship-key-from-table-B, NONE))

Re: Userelationship doen't works many to many

justinh — Mon, 29 Apr 2024 15:14:12 GMT

Rift
Thank you so much for this answer. I too have an active 1-to-many and a deactivated many-to-many. I was expecting USERELATIONSHIP to work, but it did not. Your answer saved me.