cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## 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 🙂

1 ACCEPTED SOLUTION
Resolver II

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 9
Microsoft Employee

Hi @KriZo,

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

Regards

Resolver II

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?

Microsoft Employee

Hi @KriZo,

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?

```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

Resolver II

@v-ljerr-msft

It works perfectly, thanks!

One last request, while playing around with it, I noticed that if i only mark 2018, as the year i am interessted in looking at, the OTP is 0.5. As orderline 2510Bike was partially delivered first time in 2017 and 2018. I need it to clear the arrival date filter, forgot to say that. I assume i need to use the All () function on [OrderLine Arrival Date], but i am not sure where to apply it, in your function.

And secondly there is an issue with a wrong grand total. If i have this table for instance.

The grand total should be 300%/3 unique orderlineID = 100%, but it returns 166,67%

Resolver II

Nevermind mind second part. Solved it with

`SUMX(DelOrder;[OTP]/DISTINCTCOUNT(DelOrder[OrderLineID]))`
Resolver II

Hmm I was a bit too fast. My total is still incorrect.

As you can se here, the OTP result is 133,33%, it should be 66,67%.

(100+100+0)/3=66,67%

Resolver II

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.

Resolver II

I think showing with this table might make more sense.

I need to Power BI, to basically exclude all rows, that are not the first time arrival and then sum the On Time Value.

So in this case, there are 2 orderlines, each line has been partially delivered 3 times. Some of them was on time, some where not. But i only want it to sum the On Time values from the first delivery of an order line.

Continued Contributor

Hi @KriZo,

Can you provide a dataset as sample ?

Ricardo