Forum Discussion
Helper IIIDAX to pick up value
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
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.
4 Replies
- JarroVGIT
Resident Rockstar
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.
- jdbuchanan71
Super User
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.
- 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" ) - 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.
