Forum Discussion

NotebookEnjoyer's avatar
NotebookEnjoyer
Advocate II
4 months ago
Solved

USERELATIONSHIP problems with n:m-relationship

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

  • Abdullahmajid1's avatar
    Abdullahmajid1
    4 months ago

    Try this:

     

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

15 Replies

  • techies's avatar
    techies
    Super User
    4 months ago

    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's avatar
      NotebookEnjoyer
      Advocate II
      4 months ago

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

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    4 months ago

    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] )
    )
  • johnt75's avatar
    johnt75
    Super User
    4 months ago

    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's avatar
      NotebookEnjoyer
      Advocate II
      4 months ago

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

      • johnt75's avatar
        johnt75
        Super User
        4 months ago

        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.

  • Abdullahmajid1's avatar
    Abdullahmajid1
    Regular Visitor
    4 months ago

    Try this:

     

    Sum F1 by F2 :=
CALCULATE(
    SUM('Fact_1'[Value_F1]),
    TREATAS(
        VALUES('Fact_2'[Key_D1]),
        'Fact_1'[Key_D1]
    ),
    REMOVEFILTERS('Map1')
)
  • Xian-Zuo's avatar
    Xian-Zuo
    Frequent Visitor
    4 months ago


    you can try this.

    measure = 
CALCULATE(
  [Measure]
   ,Map1
)

     

  • v-priyankata's avatar
    v-priyankata
    Community Support
    4 months ago

    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.