count the number of orders

[mina97]

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 

[mina97]

i used the same data as you it did NOT 😐 

[Dangar332]

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 

[mina97]

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

[Anonymous · Accepted solution]

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

[mina97]

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

[mina97]

wrong it gives 0 😞

[Dangar332]

Hi, @mina97 

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

For above data it give 1 as a output 

[Dangar332]

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

[mina97]

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 

[mina97]

@rubayatyasmin 😞

[rubayatyasmin]

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

Thanks 

[rubayatyasmin]

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

[mina97]

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