nth occurence of a value

[Marion]

Hi,

Id like PowerBI to give me the nth occurrence of a value, in DAX or M language.

For each SKU that can appear several times, I have several potential suppliers. Id like to display the nth vendor per row as follows :

Date	SKU	Vendor	1st vendor	2nd vendor
2021-08-06	Apple	Franck	Franck	Franck
2021-09-07	Strawberry	Mary	Mary	Franck
2021-09-20	Strawberry	Franck	Mary	Franck
2021-09-21	Pineapple	Paul	Paul	null
2021-11-10	Apple	Mary	Franck	Franck
2021-11-15	Cherry	Paul	Paul	null

Thanks for your help

[smpa01]

@Marion  you can utilize the following measures

1stVendor = 
CALCULATE (
    MAXX (
        FILTER (
            ADDCOLUMNS (
                'Table',
                "rank",
                    RANKX (
                        FILTER ( 'Table', EARLIER ( 'Table'[SKU] ) = 'Table'[SKU] ),
                        CALCULATE ( MAX ( 'Table'[Date] ) ),
                        ,
                        DESC
                    )
            ),
            [rank] = 1
        ),
        [Vendor]
    ),
    ALLEXCEPT ( 'Table', 'Table'[SKU] )
)

2ndVendor = 
CALCULATE (
    MAXX (
        FILTER (
            ADDCOLUMNS (
                'Table',
                "rank",
                    RANKX (
                        FILTER ( 'Table', EARLIER ( 'Table'[SKU] ) = 'Table'[SKU] ),
                        CALCULATE ( MAX ( 'Table'[Date] ) ),
                        ,
                        DESC
                    )
            ),
            [rank] = 2
        ),
        [Vendor]
    ),
    ALLEXCEPT ( 'Table', 'Table'[SKU] )
)

[Marion]

Thank you smpa01 ! Looks like it is the right formula.

However I have the following error message when I copy-paste the formula for the 1st vendor in a new column: 

A circular dependency was detected: Table[Column]

do you know how to fix it?

Thanks a lot

[smpa01 · Accepted solution]

Hi @Marion , the solutions are to be utlized as measures and not calculated columns. I f you refer to the attached pbix, you will see.

[Marion]

It works thank you !

[smpa01]

@Marion  you will get nth occurnece by SKU with the following measure

Measure =
CALCULATE (
    MAX ( 'Table'[1st vendor] ),
    FILTER (
        'Table',
        'Table'[Date]
            = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[SKU] ) )
    )
)

[Marion]

Hi smpa01 thanks for your answer.

Columns "1st vendor" and "2nd vendor" are the outputs Id like PowerBI to generate.

The initial database only contains colums "date", "sku" and "vendor".

[Marion]

Sorry I made a mistake in my table :

Date	SKU	Vendor	1st vendor	2nd vendor
2021-08-06	Apple	Franck	Franck	Mary
2021-09-07	Strawberry	Mary	Mary	Franck
2021-09-20	Strawberry	Franck	Mary	Franck
2021-09-21	Pineapple	Paul	Paul	null
2021-11-10	Apple	Mary	Franck	Mary
2021-11-15	Cherry	Paul	Paul	null