Unique value count only at start date

[bdeleur]

Hi,

I have a datebase and I need to count only the first row of the unique reference.

So it looks like this:

Reference	Start date
1	1-1-2023 17:00
2	1-1-2023 16:23
1	12-1-2023 10:34

The expected result is:

1-1-2023 2 (reference 1 and 2) and specified at hour: 1 at 16:00 (reference 2) en 17:00 1 (reference 1)

and no count for reference 1 on 12-1-2023

I can get the count when the reference is present but I only wants the first and ignore the rest.

[v-zhangti · Accepted solution]

Hi, @bdeleur 

You can try the following methods.

Column = 
Var _count= CALCULATE(COUNT('Table'[Reference]),ALLEXCEPT('Table','Table'[Start date]))
Var _mindate=CALCULATE(MIN('Table'[Start date]),ALLEXCEPT('Table','Table'[Reference]))
Return
IF([Start date]=_mindate,_count,BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[bdeleur]

Hi @v-zhangti,

Tnx for you answer.

When I apply the code at my database I think it is almost correct. When I use a filter on the column with 1 I get the first but sometimes I see a different number (2 till 6). When I filter on the reference that is the only result.

I think that I get the result I want when I filter out the blank results but why generates the higher numbers?

I'll validate a bit more to see what exactly is happening.

Edit: I get it now. On the exact date and time there a 3 results in the database. It generates a 3 because it's number 3 of the reference numbers. But that is okay and no problem.

I'll do the validating but it seems alright. I'll mark your answer as the solution!

Tnx!!

[bdeleur]

Anybody an suggestion?