Help Solve Error Message: duplicated rows are encountered in INDEX's Relation paramter

Hi etane,

I hope you are doing well ☺️❤️

So When you use MATCHBY(ORDER#) within each PARTITIONBY(CUSTOMER_ID) Power BI expects that each order number is unique for each customer So the error suggests there might be:

• Duplicate ORDER# values for the same customer
• Or some other issues with uniqueness in the filtered dataset

How to Solve This?

First Approach : (If you have SQL access)

• you can check for duplicate ORDER# within customer partitions

SELECT CUSTOMER_ID, ORDER#, COUNT(*) 
FROM SALESTABLE 
WHERE PRODUCTYPE = 'A' AND ORDERSTATUS = 'COMPLETE'
GROUP BY CUSTOMER_ID, ORDER#
HAVING COUNT(*) > 1

Second Approach : (That's What you need)

This finds the earliest sales date for each customer without complex partitioning logic Using MIN with FILTER:

First Purchase Date Product A =
VAR CurrentCustomer = 'CustomerTable'[CUSTOMER_ID]
RETURN
CALCULATE(
    MIN('SALESTABLE'[SALESDATE]),
    FILTER(
        'SALESTABLE',
        'SALESTABLE'[CUSTOMER_ID] = CurrentCustomer &&
        'SALESTABLE'[PRODUCTYPE] = "A" &&
        'SALESTABLE'[ORDERSTATUS] = "COMPLETE"
    )
)

• if you have a single order per date Use FIRSTNONBLANK 

First Purchase Date Product A =
CALCULATE(
    FIRSTNONBLANK(
        'SALESTABLE'[SALESDATE],
        1
    ),
    'SALESTABLE'[PRODUCTYPE] = "A",
    'SALESTABLE'[ORDERSTATUS] = "COMPLETE"
)

• Also instead of these you can use SUMMARIZE to get distinct transactions first

First Purchase Date Product A =
MAXX(
    TOPN(
        1,
        CALCULATETABLE(
            VALUES('SALESTABLE'[SALESDATE]),
            'SALESTABLE'[PRODUCTYPE] = "A",
            'SALESTABLE'[ORDERSTATUS] = "COMPLETE"
        ),
        'SALESTABLE'[SALESDATE], ASC
    ),
    'SALESTABLE'[SALESDATE]
)

Third Approach:(For handling tie breaking)

• If you need to handle ties where multiple orders have the same earliest date use MINX with TOPN

First Purchase Date Product A =
VAR FirstTransaction =
    TOPN(
        1,
        FILTER(
            'SALESTABLE',
            'SALESTABLE'[CUSTOMER_ID] = 'CustomerTable'[CUSTOMER_ID] &&
            'SALESTABLE'[PRODUCTYPE] = "A" &&
            'SALESTABLE'[ORDERSTATUS] = "COMPLETE"
        ),
        'SALESTABLE'[SALESDATE], ASC,
        'SALESTABLE'[ORDER#], ASC
    )
RETURN
MINX(FirstTransaction, 'SALESTABLE'[SALESDATE])

Bonus Approach:(Debug the duplicates directly)

• Create a temporary table to check for duplicates

Duplicate Check = 
SUMMARIZE(
    FILTER('SALESTABLE', 'SALESTABLE'[PRODUCTYPE] = "A" && 'SALESTABLE'[ORDERSTATUS] = "COMPLETE"),
    'SALESTABLE'[CUSTOMER_ID],
    'SALESTABLE'[ORDER#],
    "Count", COUNTROWS('SALESTABLE')
)

Hi, 

If you want to have ranking  and avoid index you can use RANX. E.g.

Thank you for all the responses.  

After hours of tinkering, I found a solution (mainly thanks to ChatGPT).

Although I couldn't find any duplicate Order#s, I augmented the MatchBy statement by adding row ID to ensure the DAX isn't held up by a duplicate Order#:

CALCULATE(
     SELECTEDVALUE('SALESTABLE'[SALESDATE]),

     INDEX(1,

     FILTER('SALESTABLE', PRODUCTYPE = "A' && ORDERSTATUS = "COMPLETE"),
     ORDERBY( SALESTABLE'[SALESDATE], ASC, SALESTABLE'[ORDER#],ASC),

     PARTITIONBY(SALESTABLE'[CUSTOMER_ID]),

     MATCHBY(SALESTABLE'[ORDER#], SALESTABLE'[Id]
     )
    ,ALLEXCEPT('SALESTABLE', 'SALESTABLE'[CUSTOMER_ID])

    , SALESTABLE'[SALESDATE]

)