Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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))
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))
@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.
@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
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
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |