Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum only first entry

Hi

 

I have been trying to figure this out for a few days now. I am very new to DAX language, but pretty good at googeling, and figuring out how to change what ever functions i might find, but this time i haven't succeeded. 

 

I have a bunch of columns and rows that contains arrival of items from suppliers. in a new colum i have created a formula that returns the value 1 if the line was delivered timely, and 0 if it wasn't, column named [On time]. However as it often occurs, a supplier might deliver a line partially. What i want is to sum only the values, for the first entries. 

 

 Each row has an orderline ID which is a concatanation of order No, Orderline no, Item. 

 

So i need a Dax formula that checks whether the [Orderline ID] exists more than once, and if it does, only return the value from [On Time], from the first time this orderline was delivered. [Orderline Arrival Date]. 

 

Hope it makes sense :) 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Sorry for all my posts, i promise this is the last :) Just wanted to show, what it ended up being. The solution provided by v-ljerr-msft kept on making issues for me, so i reverted back to the firstnonblank solution. And this was the final, which does excactly what i want everywhere. 

     

    OTP = CALCULATE(SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID]);DISTINCT(DelOrder[OrderLineID]);ALL(DelOrder[Orderline arrival date]))

    Never the less, thanks for the help. v-ljerr-msft now i know how to use VAR, and i kickstarted som though processes, that eventually lead to my final solution. 

9 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue? It's better to share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Sure thing, here is an example. 

       

      https://www.dropbox.com/s/orvdzha2wi36htq/Supplier%20Performance%20Example.pbix?dl=0

       

      I actually managed to solve it my self, in a way. In the Query editor I sort the arrival date by so the earliest is in the beginning. And then i use this formula:

       

      OTP = SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID])

      Now it only sums the earliest On Time value, of a delivered orderline. , this works, but maybe there is a different solution, in case i hadn't sorted the data?

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

        Now it only sums the earliest On Time value, of a delivered orderline. , this works, but maybe there is a different solution, in case i hadn't sorted the data?

        Could you try using the formula below to see if it works in this scenario? :smileyhappy:

        OTP2 =
SUMX (
    DISTINCT ( DelOrder[OrderLineID] );
    VAR firstArrivalDate =
        CALCULATE ( MIN ( DelOrder[Orderline Arrival Date] ) )
    RETURN
        CALCULATE (
            SUM ( DelOrder[On Time] );
            FILTER ( DelOrder; DelOrder[Orderline Arrival Date] = firstArrivalDate )
        )
)
    / DISTINCTCOUNT ( DelOrder[OrderLineID] )

         

        Regards