Userelationship doen't works many to many

[Rift]

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?

[Rift · Accepted solution]

Hi

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))

[justinh]

@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. 

[BeaBF]

@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).

BF

[Daryl-Lynch-Bzy]

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