Forum Discussion

NotebookEnjoyer's avatar
NotebookEnjoyer
Icon for Advocate II rankAdvocate 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]),    ...
  • 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')
)
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
    Icon for Advocate II rankAdvocate 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
      Icon for Super User rankSuper 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.

      • NotebookEnjoyer's avatar
        NotebookEnjoyer
        Icon for Advocate II rankAdvocate II
        4 months ago

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

      • NotebookEnjoyer's avatar
        NotebookEnjoyer
        Icon for Advocate II rankAdvocate II
        4 months ago

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