DAX measure related tables two ID columns in fact table

Question

I have a scenario in which I have a fact table and related dim table. The fact table is related via ID field, but also has a second depth on a row basis which means there are two IDs' for the same row. My question is if it's possible to create a measure that will sum up once by related column and next by secondary ID column. Creating regular measure gives awkward results for the secondaryID.

I am attaching simple scenario as a reference for what I am asking about.

Thank you in advance!
Cheers

Not sure I quite understand. Which is your input and what is the desired output? — Alexis Olson

Olly Olly · Accepted Answer · 2018-11-26T08:34:35

Create an active relationship between ID on both tables, and an inactive relationship between Dim[ID] and Fact[SecondaryID].

Now you can use USERELATIONSHIP to calculate the secondary value per ID.

Measure = 
VAR Primary = 
    SUM ( 'Fact'[Value] )
VAR Secondary = 
    CALCULATE ( 
        SUM ( 'Fact'[SecondaryValue] ), 
        USERELATIONSHIP ( Dim[ID], 'Fact'[SecondaryID] )
    )
RETURN
    Primary + Secondary

EDIT: As you've explained you are using Excel 2013, you can't use VAR - so simply change the measure to:

Measure = 
SUM ( 'Fact'[Value] ) + 
CALCULATE ( 
    SUM ( 'Fact'[SecondaryValue] ), 
    USERELATIONSHIP ( Dim[ID], 'Fact'[SecondaryID] )
)

DAX measure related tables two ID columns in fact table

1 Answers