Skip to main content
cancel
Showing results for 
Search instead 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

Reply
KriZo
Resolver II
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

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. 

View solution in original post

9 REPLIES 9
v-ljerr-msft
Microsoft Employee
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 uploadingSmiley Happy

 

Regards

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?

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? Smiley Happy

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

@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. 

1.PNG

 

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

Udklip.PNG

 

 

Nevermind mind second part. Solved it with 

 

SUMX(DelOrder;[OTP]/DISTINCTCOUNT(DelOrder[OrderLineID]))

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%

Udklip.PNG

 

 

 

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. 

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. 

 

Udklip.PNG

ricardocamargos
Continued Contributor
Continued Contributor

Hi @KriZo,

 

Can you provide a dataset as sample ?

 

Ricardo

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors