USERELATIONSHIP problems with n:m-relationship

[NotebookEnjoyer]

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

[v-priyankata]

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.

[Xian-Zuo]

you can try this.

measure = 
CALCULATE(
  [Measure]
   ,Map1
)

[Abdullahmajid1 · Accepted solution]

Try this:

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

[v-priyankata]

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.

[cengizhanarslan]

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

[NotebookEnjoyer]

Unfortunately this doesn't differentiate correctly for D1.

[techies]

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

[NotebookEnjoyer]

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

[johnt75]

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

[NotebookEnjoyer]

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

[FBergamaschi]

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

[NotebookEnjoyer]

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

[johnt75]

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.

[NotebookEnjoyer]

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

[johnt75]

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