Nth Occurrence of ID by date or Index

[Anonymous]

Hi all, 

I've been combing the internet for a day's worth of work and have had no luck applying any solutions I've found for my problem. My DAX knowledge isn't the best.

I want to create a calculated column that tells us what Nth occurence is this ID in the table with respect to date. I also have an Index column that could be used similar to the date as the lower the Index number, the earlier the date.

An example of the output would be something like this

I have sought solutions from here: https://community.powerbi.com/t5/Desktop/Count-Nth-of-Occurrence/m-p/806080#M387708

But this didn't work and just gave me 1s in each row.

I also found this https://community.powerbi.com/t5/Desktop/DAX-to-count-occurrence-number-of-a-value-in-a-column/m-p/8... but couldn't translate the changes in the query editor to my own dataset.

Any help would be appreciated

Kind regards,

J

[mahoneypat]

If you add an index column as you suggest, here is one way to do it in your measure:

Occurence Number =
VAR __thisindex =
    MIN ( Occurence[Index] )
VAR __thistext =
    MIN ( Occurence[Column1] )
VAR __rankthisindex =
    RANKX (
        CALCULATETABLE (
            VALUES ( Occurence[Index] ),
            ALL ( Occurence ),
            Occurence[Column1] = __thistext
        ),
        Occurence[Index],
        __thisindex,
        ASC
    )
RETURN
    __rankthisindex

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat, 

Thanks so much for creating a solution for me.

I had no luck - I just get a 1 for each row.

Just in case I've missed something: 

This is what mine looked like where my table is called ForwardOrderBook and my ID column is called Project WBS and of course my Index column is there too.

Any ideas why it wouldn't have worked?

Thanks, 

J

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

You can refer to the following calculated column after you add an index column:

Column = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) && 'Table'[Index] <= EARLIER('Table'[Index])))

For more details ,please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfVyY87ab_5Dv6JGm-YnYT4BjYem0JQ8BnzfqvPMkSoJ8Q?e=rPasak

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

Best Regards,

Dedmon Dai

[Anonymous]

Thank you Dedmon, this has worked for me 🙂

I had applied this sort of formula before but Earlier wasn't working so not sure what's different this time!

I appreciate it, thanks again