Forum Discussion
Solved
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....
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
Community Champion
Community ChampionMarion 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.
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!
