Display nth match

[Marion]

Hi

Id like to display the nth match for each row of a dataset.

Here is my scenario:

In my first table, I have SKUs and date of sale.

In my second table, I have SKUs and potential suppliers.

There is a many to many relationship between the 2 tables on the SKU column. I merged them so I can have a view on the potential suppliers for each SKU for each date of sale.

Problem is, my records have been duplicated because there are several matches of suppliers for each SKU and date. So Id like to capture the nth vendor in a column rather than in a row.

Here below is the data. The measures were provided by @smpa01 . The "First Vendor" column gives the right answer, but "Second Vendor" column is blank. It should display "Bernard"

1st vendor measure =

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 Measure =

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

)

[v-yanjiang-msft · Accepted solution]

Hi @Marion ,

In your code, I copy the rank part to a column, you can see there is no rank=2 when SKU="Apple",so it's blank.

Here's my solution.

1.Create a new table.

2.Create relationship of the two tables.

3.Create three measures.

1st = CALCULATE(MAX('Table'[Vendor]),FILTER('Table 2','Table 2'[RANK]=1))

2nd = CALCULATE(MAX('Table'[Vendor]),FILTER('Table 2','Table 2'[RANK]=2))

3rd = CALCULATE(MAX('Table'[Vendor]),FILTER('Table 2','Table 2'[RANK]=3))

I attach my sample bellow to help you to understand.

Best Regards,

Community Support Team_kalyj

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

[smpa01]

@Marion  is there any way you can recreate the data model in a small scale and attach here for me to examine please?

Please also mention the desired output for the data set you are supplying.

[Marion]

Hi smpa01

Sure here is the data and the desired output for columns :

			DESIRED OUTPUT
Date	SKU	Vendor	1st vendor	2nd vendor	3rd vendor
01-mai-21	Apple	Paul	Paul	Bernard	Franck
01-mai-21	Apple	Bernard	Paul	Bernard	Franck
10-mai-21	Apple	Paul	Paul	Bernard	Franck
10-mai-21	Apple	Bernard	Paul	Bernard	Franck
21-mai-21	Apple	Paul	Paul	Bernard	Franck
21-mai-21	Apple	Bernard	Paul	Bernard	Franck
07-juin-21	Apple	Paul	Paul	Bernard	Franck
07-juin-21	Apple	Bernard	Paul	Bernard	Franck
17-juin-21	Apple	Paul	Paul	Bernard	Franck
17-juin-21	Apple	Bernard	Paul	Bernard	Franck
06-août-21	Apple	Franck	Paul	Bernard	Franck
07-sept-21	Strawberry	Mary	Mary	Franck	null
20-sept-21	Strawberry	Franck	Mary	Franck	null
21-sept-21	Pineapple	Paul	Paul	null	null
15-nov-21	Cherry	Paul	Paul	null	null

Thanks for your help!

[amitchandak]

@Marion , Try with Dense

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

Also, order should Asc ?

[Marion]

Hi amitchandak 

Thanks for your answer

Dense doesn't change the results. 

Ive modified DESC for ASC yes