DAX to always select value from child table.

[VTHokie]

Hi,

I need a little help with some DAX to always select a value from a child table no matter the filter context.  I have simplified the problem to my core issue in excel.  I have two tables.

 Orders

OrderId

100

200

300

OrderVersions

OrderId	VersionId	Status	Value
100	1	Inactive	5
100	2	Inactive	10
100	3	Active	30
200	1	Inactive	2
200	2	Inactive	6
200	3	Active	15
200	4	Pending	20
300	1	Inactive	5
300	2	Active	8

I want a measure that will always return the Active value for the given OrderId.  I have tried =CALCULATE(SUM([Value]), OrderVersions[Status]="Active") but this only provides the value when the filter context is Status=Active.  

Any help would be appreciated.

Mike

[v-juanli-msft · Accepted solution]

Hi @VTHokie

Create a measure as below

Measure2 = CALCULATE(SUM(Sheet4[Value]),FILTER(ALLEXCEPT(Sheet4,Sheet4[OrderId]),[Status]="Active"))

Best Regards

Maggie