Forum Discussion
USERELATIONSHIP problems with n:m-relationship
Try this:
Sum F1 by F2 := CALCULATE( SUM('Fact_1'[Value_F1]), TREATAS( VALUES('Fact_2'[Key_D1]), 'Fact_1'[Key_D1] ), REMOVEFILTERS('Map1') )
Not sure if this will work but you could try
Sum by inactive Relationship =
VAR _Keys =
CALCULATETABLE (
VALUES ( 'Fact_2'[Key_F2] ),
USERELATIONSHIP ( 'Dim_1'[Key_D1], 'Fact_2'[Key_D1] )
)
VAR Result =
SUMX ( _Keys, CALCULATE ( SUM ( 'Fact_1'[Value_F1] ) ) )
RETURN
Result
Doesn't work, unfortunately. Could it be that the calculated "_Keys"-table does not have the original relationships anymore?
I don't think that that is the problem.
Can you create a measure like
Test Sum = SUMX ( 'Fact_2', CALCULATE ( SUM ( 'Fact_1'[Value_F1] ) ) )and put that into a table / matrix with 'Fact_2'[Key_D1]. This isn't using the inactive relationship at all so should tell us if the active relationships are working as intended.
In short, it works. (In long, I have to add a filter because for each entry there is each one positive and negative entry, canceling each other out, and I have left out of my model the dimension table by which this is filtered. But it works.)
Try
Sum by inactive Relationship = VAR _Keys = CALCULATETABLE ( VALUES ( 'Fact_2'[Key_F2] ), USERELATIONSHIP ( 'Dim_1'[Key_D1], 'Fact_2'[Key_D1] ) ) VAR Result = CALCULATE ( SUM ( 'Fact_1'[Value_F1] ), TREATAS ( _Keys, 'Fact_1'[Key_F2] ) ) RETURN Result
looks like we need a REMOVEFILTERS due to context transition, can you shar ethe pbix ?
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Unfortunately I can't, because the real model contains company data (and rebuilding the whole thing as mock-up does not yet seem proportional).
