Forum Discussion

Marion's avatar
Marion
Frequent Visitor
4 years ago
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....
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    Marion
    Frequent Visitor
    4 years ago

    Hi amitchandak 

    Thanks for your answer

    Dense doesn't change the results. 

    Ive modified DESC for ASC yes