Forum Discussion
Display nth match
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"
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.
5 Replies
- amitchandak
Super User
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 ?
Hi amitchandak
Thanks for your answer
Dense doesn't change the results.
Ive modified DESC for ASC yes
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!
v-yanjiang-msftCommunity Support
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.
