Forum Discussion

topazz11's avatar
topazz11
Icon for Helper III rankHelper III
6 years ago
Solved

DAX 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 t...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

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.