DAX to pick up value

[topazz11]

Hello, I would like to create a measure that returns the cost with conditions.

The conditions are 'highest Seq', 'Current', and 'Approved' per Item ID (Please see below pic)

Anyone know how to do this? Thank you

[Ashish_Mathur]

Hi,

Drag Item ID to the Row labels and write these 2 measures

Last sequence number = CALCULATE(LASTNONBLANK(Data[Seq],1),Data[Status]="Approved",Data[Curr Ind]="Current")

Costs = LOOKUPVALUE(Data[Cost],Data[Seq],[Last sequence number],Data[Status],"Approved",Data[Curr Ind],"Current")

I see no sense in showing Curr Ind and Status in the visual because they are the very conditions for our calculations.

Hope this helps.

[JarroVGIT · Accepted solution]

Well, interesting question! I've copied your dataset into PBI and got to work. This seems to work;

MaxSeqCurrentApproved = CALCULATE(SUM('Table'[Cost]), FILTER('Table', 'Table'[Seq] = CALCULATE(MAX('Table'[Seq]), FILTER('Table', 'Table'[CurrInd] = "Current" && 'Table'[Status] = "Approved"))))

Explaination:We take the sum of Cost -> although it is just one row

with a Filter on the table, -> here is the tricky part

where Seq = the Max seq with a filter -> this will work where the context is sliced by Item btw,

where CurrInd = Curent AND Status = Approved -> having the Seq with Current and Status set correctly is enough, we don't need a second filter on these fields.

Ain't pretty but it works in my test (which is a matrix with rows=ItemID (not summarized) and values=Measure) 🙂

Please mark as solution if this answers your question.

[parry2k]

@topazz11 or try this

Total Cost = 
VAR __seq = CALCULATE( MAX ( Pickup[Seq] ), ALLEXCEPT ( Pickup, Pickup[Item Id] ), Pickup[Curr Ind] = "Current", Pickup[Status] = "Approved" )
RETURN
CALCULATE ( SUM ( Pickup[Cost] ), Pickup[Seq] = __seq, Pickup[Curr Ind] = "Current", Pickup[Status] = "Approved"  )

[jdbuchanan71]

Hello @topazz11 

I'm going to assume you want the returned costs to add up correctly so this measure is a SUMX.  It also returns a value for [Item ID] 42180 from your sample since that item ID has a line matches the criteria ("Current", "Approved" ).

Measure = 
SUMX ( VALUES ( 'Table'[Item ID] ),
    VAR _Seq = 
        CALCULATE ( 
            MAX ( 'Table'[Seq] )
            ,ALLEXCEPT ( 'Table', 'Table'[Item ID] )
            ,'Table'[Curr Ind] = "Current" 
            ,'Table'[Status] = "Approved"
        )
    RETURN
        CALCULATE( 
            SUM ( 'Table'[Cost] )
            ,KEEPFILTERS( 'Table'[Curr Ind] = "Current" )
            ,KEEPFILTERS( 'Table'[Status] = "Approved" )
            ,KEEPFILTERS( 'Table'[Seq] = _Seq )
        )
)

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.