Forum Discussion

EWBWEBB's avatar
EWBWEBB
Helper III
3 years ago
Solved

Counting instances

Hi there

 

I am trying to count the instances of Covid over a period of time.

 

There are two piece to this.

1.) how do I count the instances where the first instance would be labelled 1 and the second as 2, third as 3 and so on. Using earlier seems to mark them all as the highest count. so the first second and third would all show a count of 3.

 

2.) I'd like to do this to see if the number of multiple instances has changed year on year. So count the first one in the year as 1, second as 2 then the next year restart the count at 1, and so on.

 

My data is laid out as follows 

From DateUNID
19/03/2020F005F
15/11/2020F0040
20/01/2021Y8F44
01/03/2021F00FF
04/03/2021F0078
09/06/2021Y8F08
30/06/2021F005F
20/07/2021a0588
30/07/2021F00FF
14/09/2021F00FF
25/09/2021F00FF
19/11/2021F0090
12/12/2021Y8F44
03/01/2022a058F
23/05/2022a0584
01/06/2022a0588
05/07/2022F00FF
11/07/2022F0078

 

I have a caelndar (simple just every day of the year) joined on from date 1-Many.

 

This would be my expected result.

 

  expected result 
  Total CountIn Year Count
From DateUNID  
19/03/2020F005F11
15/11/2020F004011
20/01/2021Y8F4411
01/03/2021F00FF11
04/03/2021F007811
09/06/2021Y8F0811
30/06/2021F005F21
20/07/2021a058811
30/07/2021F00FF22
14/09/2021F00FF33
25/09/2021F00FF44
19/11/2021F009011
12/12/2021Y8F4422
03/01/2022a058F11
23/05/2022a058411
01/06/2022a058821
05/07/2022F00FF51
11/07/2022F007821

 

From this I would then like to be able to count the number of people with multiple instances in each year and the average number of instances - which should be pretty straight forward if this is a calculated column. 

 

COUNT
instance
occurunce
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Total count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

     

    In year count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

     

    ewbwebb.pbix18 KB
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your reply.

    I am not sure if I correctly understood your second questions, but please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

    Expected CC =
VAR _toponebyyeartable =
    TOPN (
        1,
        FILTER (
            Data,
            Data[UNID] = EARLIER ( Data[UNID] )
                && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
        ),
        Data[In year count CC], DESC
    )
RETURN
    IF (
        MAXX ( _toponebyyeartable, Data[In year count CC] ) = Data[In year count CC],
        Data[In year count CC]
    )
    ewbwebb.pbix19 KB

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Total count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

     

    In year count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

     

    ewbwebb.pbix18 KB
  • EWBWEBB's avatar
    EWBWEBB
    Helper III
    3 years ago

    Jihwan_Kim This is perfect thank you.

    So the next step - Is there a way that just the most recent instance in each year is marked?

    I.e for those that have just 1 instance in a year we get a 1, for those that have had 2 instances in a year the first instance in blank and the second is mark as 2 and so on?

    When I count this the 1 is throwing the figures off a little as someone with 2 or more instances is also appearing in the column counting their first and second instance as well.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      3 years ago

      Hi,

      Thank you for your reply.

      I am not sure if I correctly understood your second questions, but please check the below picture and the attached pbix file whether it suits your requirement.

       

       

       

      Expected CC =
VAR _toponebyyeartable =
    TOPN (
        1,
        FILTER (
            Data,
            Data[UNID] = EARLIER ( Data[UNID] )
                && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
        ),
        Data[In year count CC], DESC
    )
RETURN
    IF (
        MAXX ( _toponebyyeartable, Data[In year count CC] ) = Data[In year count CC],
        Data[In year count CC]
    )
      ewbwebb.pbix19 KB