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...
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] )
    )
)

     

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

    Spoth on Jihwan_Kim - thank you so much