topic Only count a order the first time in DAX Commands and Tips

Only count a order the first time

Wengel — Fri, 12 Aug 2022 07:39:57 GMT

Maybe there is an easy fix to this problem, that i just havent been able to figure out.

I need to count order numbers on a specific date, but i only want to count the order number once, and it has to be the first time it appears. So if order number 213, have date 31/01/2022, and there is another entry on order number 213, on date 24/02/2022. I only want it to be counted the first time.

Hope it make sense, and there is a fix to it 🙂

Re: Only count a order the first time

johnt75 — Fri, 12 Aug 2022 09:35:59 GMT

You could create a calculated column to store the first order date and then link that to your Date table instead of, or as well as, linking the existing order date column.

The new column could be something like

First order date = CALCULATE( MIN('Sales'[Order date]), ALLEXCEPT('Sales', 'Sales'[Order ID]) )

and your number of orders measure would be either a simple DISTINCTCOUNT or a DISTINCTCOUNT with USERELATIONSHIP if you have multiple relationships from Date to Sales.

Re: Only count a order the first time

Anonymous — Tue, 16 Aug 2022 20:41:33 GMT

Awesome solution!

I got the same problem in here and this solutions tends to solve at least 85% of the situation..

But i have another doubt:

In this dax function created to find the min(value), is there anyway to get the min(value) that has another condition?

For example:

ID | Date | Status
1--|10/01| ERROR
2--|11/01| ACCEPTED
3--|13/01| ACCEPTED
4--|18/01| ERROR

If i apply the query itself, it will return me the ID 1, since it has the min value, but instead, i would like to get the ID 2, since it is the min value accepted..

This question makes any sense? Sorry for any inconvenience 😛

Re: Only count a order the first time

Anonymous — Tue, 16 Aug 2022 21:06:58 GMT

I got myself reading documentation and found the solution!

FIRST VALUE ACCEPTED =
CALCULATE(
MINX( FILTER(Table, [Status] = "ACCEPTED"),Table[Date]),
ALLEXCEPT(Table,Table[Id])
)

Re: Only count a order the first time

Anonymous — Fri, 19 Aug 2022 01:31:05 GMT

Hi Wengel ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag = IF( MAX('Table'[Date])=MINX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),[Date]),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly