topic Re: USERELATIONSHIP problems with n:m-relationship in DAX Commands and Tips

USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Thu, 16 Apr 2026 09:07:55 GMT

Hello everyone,

I have an arrangement as follows:

Therein, the following measure unfortunately yields blank values:

Sum by inactive Relationship = SUMX(
VALUES('Fact_2'[Key_F2]),
CALCULATE(
SUM('Fact_1'[Value_F1]), // Any aggregation really
USERELATIONSHIP(
'Dim_1'[Key_D1],
'Fact_2'[Key_D1]
)
)
)

The goal is a table/matrix visual like this:

My best guess is that if I force to use the inactive relationship, it is unclear which other relationship is to be ignored?

ChatGPT recommended:

Sum F1 by F2 =
CALCULATE(
    SUM('Fact_1'[Value_F1]),
    TREATAS(
        VALUES('Fact_2'[Key_D1]),
        'Fact_1'[Key_D1]
    )
)

This gives a value for the sum, but only one with a blank Key_D1, not differentiating.

Thanks for any help! (And please, if you write DAX code, I would really appreciate at least some formatting and not colossal one-liners.)

Re: USERELATIONSHIP problems with n:m-relationship

johnt75 — Thu, 16 Apr 2026 07:57:11 GMT

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

Re: USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Thu, 16 Apr 2026 09:05:34 GMT

Doesn't work, unfortunately. Could it be that the calculated "_Keys"-table does not have the original relationships anymore?

Re: USERELATIONSHIP problems with n:m-relationship

johnt75 — Thu, 16 Apr 2026 09:23:16 GMT

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.

Re: USERELATIONSHIP problems with n:m-relationship

techies — Thu, 16 Apr 2026 10:46:14 GMT

Hi NotebookEnjoyer please try this

Sum by inactive Relationship = VAR _CurrentKey = SELECTEDVALUE ( 'Dim_1'[Key_D1] )

VAR _Keys =

CALCULATETABLE (

VALUES ( 'Fact_2'[Key_F2] ),

TREATAS ( { _CurrentKey }, 'Fact_2'[Key_D1] )

)

VAR Result =

CALCULATE (

SUM ( 'Fact_1'[Value_F1] ),

TREATAS ( _Keys, 'Map1'[Key_F2] )

)

RETURN

Result

Re: USERELATIONSHIP problems with n:m-relationship

cengizhanarslan — Thu, 16 Apr 2026 20:25:19 GMT

Please try the measure below:

Sum by inactive Relationship = VAR _Keys = VALUES( 'Fact_2'[Key_D1] ) RETURN CALCULATE( SUM( 'Fact_1'[Value_F1] ), TREATAS( _Keys, 'Dim_1'[Key_D1] ) )

Re: USERELATIONSHIP problems with n:m-relationship

FBergamaschi — Fri, 17 Apr 2026 07:11:30 GMT

Hi NotebookEnjoyer

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

Re: USERELATIONSHIP problems with n:m-relationship

v-priyankata — Mon, 20 Apr 2026 10:01:47 GMT

Hi NotebookEnjoyer

Thank you for reaching out to the Microsoft Fabric Forum Community.

johnt75 techies cengizhanarslan FBergamaschi Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

Re: USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Tue, 21 Apr 2026 13:18:27 GMT

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

Re: USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Tue, 21 Apr 2026 13:20:33 GMT

Unfortunately I can't, because the real model contains company data (and rebuilding the whole thing as mock-up does not yet seem proportional).

Re: USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Tue, 21 Apr 2026 13:24:11 GMT

Unfortunately this doesn't differentiate correctly for D1.

Re: USERELATIONSHIP problems with n:m-relationship

NotebookEnjoyer — Tue, 21 Apr 2026 13:37:41 GMT

It's not quite there yet. It doesn't differentiate by the dimension and gives double the correct sum. I'm working on it.

Re: USERELATIONSHIP problems with n:m-relationship

johnt75 — Tue, 21 Apr 2026 14:15:31 GMT

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

Re: USERELATIONSHIP problems with n:m-relationship

Abdullahmajid1 — Tue, 21 Apr 2026 14:45:07 GMT

Try this:

Sum F1 by F2 := CALCULATE( SUM('Fact_1'[Value_F1]), TREATAS( VALUES('Fact_2'[Key_D1]), 'Fact_1'[Key_D1] ), REMOVEFILTERS('Map1') )

Re: USERELATIONSHIP problems with n:m-relationship

Xian-Zuo — Wed, 22 Apr 2026 05:47:28 GMT

you can try this.

measure = CALCULATE( [Measure] ,Map1 )

Re: USERELATIONSHIP problems with n:m-relationship

v-priyankata — Thu, 23 Apr 2026 04:57:27 GMT

Hi NotebookEnjoyer

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.