Forum Discussion

bdeleur's avatar
bdeleur
Helper III
3 years ago
Solved

Unique value count only at start date

Hi,

 

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

 

So it looks like this:

ReferenceStart date
11-1-2023 17:00
21-1-2023 16:23
112-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.

Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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's avatar
      bdeleur
      Helper III
      3 years ago

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