topic Re: count the number of orders in DAX Commands and Tips

count the number of orders

mina97 — Wed, 18 Oct 2023 10:39:24 GMT

i have the following data

order	stauts
216	submited
216	accepted
217	submited
217	resumited
217	accepted

i need to count the number of orders that got accepted without being reaplied so in this case it should give one order

please help and thank you

Re: count the number of orders

rubayatyasmin — Wed, 18 Oct 2023 10:58:35 GMT

Hi, mina97

I tried to solve this. here is the output.

AcceptedOrdersWithoutResubmit = CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[stauts] = "accepted" && NOT ( CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[order] = EARLIER('Table'[order]) && 'Table'[stauts] = "resumited" ) ) > 0 ) ) )

Re: count the number of orders

mina97 — Sun, 22 Oct 2023 07:08:47 GMT

hey i am really happy with the code BUT i need it to count the distict number of orders 😞

Re: count the number of orders

mina97 — Sun, 22 Oct 2023 09:53:42 GMT

rubayatyasmin 😞

Re: count the number of orders

mina97 — Mon, 23 Oct 2023 03:32:57 GMT

sorry but the output is wrong ..... it deleted the resubmitted row but it still counted the order as accepted from the first time ... the out put is = 4 where it should be 1

Re: count the number of orders

Dangar332 — Mon, 23 Oct 2023 03:44:34 GMT

Hi, mina97

try below just adjust your table aand column name

final =

var a = DISTINCTCOUNT('practice'[order])

var b = CALCULATE(DISTINCTCOUNT('practice'[order]),practice[status]="resumited")

return a-b

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos

Re: count the number of orders

mina97 — Mon, 23 Oct 2023 03:50:21 GMT

wrong it gives 0 😞

Re: count the number of orders

Dangar332 — Mon, 23 Oct 2023 03:55:00 GMT

Hi, mina97

It give 1 as a output for code which I provide .

For above data it give 1 as a output

Re: count the number of orders

mina97 — Mon, 23 Oct 2023 03:59:26 GMT

i used the same data as you it did NOT 😐

Re: count the number of orders

Dangar332 — Mon, 23 Oct 2023 04:25:58 GMT

Hi, mina97

final =

var a = DISTINCTCOUNT('status'[order])

var b = CALCULATE(DISTINCTCOUNT('status'[order]),'status'[stauts]="resumited")

return

a-b

For download .pbix click HERE

Re: count the number of orders

mina97 — Mon, 23 Oct 2023 04:27:22 GMT

you are missing the point 🙂 if i wanted this output i would have done it myself 🙂

Re: count the number of orders

mina97 — Mon, 23 Oct 2023 04:28:08 GMT

okay thank you for trying some one else will help as i wish hopfully

Re: count the number of orders

rubayatyasmin — Mon, 23 Oct 2023 08:51:44 GMT

Hi, I am a bit busy these days. I will reply ASAP.

Thanks

Re: count the number of orders

Anonymous — Fri, 27 Oct 2023 11:30:24 GMT

Hi mina97 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Flag = VAR _tab1 = CALCULATETABLE ( VALUES ( 'Table'[order] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[stauts] = "accepted" ) ) VAR _tab2 = CALCULATETABLE ( VALUES ( 'Table'[order] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[order] IN _tab1 && 'Table'[stauts] = "resumited" ) ) VAR _tab = EXCEPT ( _tab1, _tab2 ) RETURN IF ( SELECTEDVALUE ( 'Table'[order] ) IN _tab, 1, 0 )

Number of orders = SUMX(VALUES('Table'[order]),[Flag])

Best Regards