cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VTHokie Frequent Visitor
Frequent Visitor

DAX to always select value from child table.

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

OrderIdVersionIdStatusValue
1001Inactive5
1002Inactive10
1003Active30
2001Inactive2
2002Inactive6
2003Active15
2004Pending20
3001Inactive5
3002Active8

 

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.  

 

Capture.PNG

 

Any help would be appreciated.

 

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX to always select value from child table.

Hi @VTHokie

Create a measure as below

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

6.png

 

Best Regards

Maggie

1 REPLY 1
Community Support Team
Community Support Team

Re: DAX to always select value from child table.

Hi @VTHokie

Create a measure as below

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

6.png

 

Best Regards

Maggie