Forum Discussion
Count Ordernumbers base on duplicate value
Hello Applicable88
A measure like this should work:
Performance = VAR Orders_D = CALCULATETABLE ( VALUES ( Orders[Ordernumber] ), Orders[Status] = "D" ) VAR Orders_A = CALCULATETABLE ( VALUES ( Orders[Ordernumber] ), Orders[Status] = "A" ) RETURN COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.
With your sample data,
- Orders_D = { 1111, 2222, 3333, 4444, 5555 }
- Orders_A = { 1111, 2222, 3333, 7777, 8888 }
- EXCEPT ( Orders_D, Orders_A ) = { 4444, 5555 }
Regards,
Owen
Hello Applicable88
A measure like this should work:
Performance =
VAR Orders_D =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "D"
)
VAR Orders_A =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "A"
)
RETURN
COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.
With your sample data,
- Orders_D = { 1111, 2222, 3333, 4444, 5555 }
- Orders_A = { 1111, 2222, 3333, 7777, 8888 }
- EXCEPT ( Orders_D, Orders_A ) = { 4444, 5555 }
Regards,
Owen
Thank you very much it works for the ordernumbers.
I forgot to mention that I have another column (Positions of deliveries) which this time needs to be sum up base on the above logic, so only sum these delivery positions of the 2 orders. I tried with calculate sum and sumx as well, but I always get an error or empty value and sometimes a much bigger number. As you can see I changed the last line to sum instead of count:
Performance =
VAR Orders_D =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "D"
)
VAR Orders_A =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "A"
)
RETURN
calculate( SUMX(Orders,Orders[Deliverypositions]), EXCEPT ( Orders_D, Orders_A ) )
Do I need a comlete another formula to get sum the position?