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

[etane]

Hello.

I've summarized our sales table to grab unique customers to its own table.  This table is linked to the sales table by customer id.  I then added a calculated column to indicate the customers' first completed transaction date for Product A.  The DAX looks something like this:

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#]
     )
    ,ALLEXCEPT('SALESTABLE', 'SALESTABLE'[CUSTOMER_ID])

    , SALESTABLE'[SALESDATE]

)

So, I have a customer table with unique customers with their respective first purchase date that's linked to the sales table.

This worked for about a year.  But, yesterday, the refresh for this report stopped.  I get this message: 

When I look into the first purchase date Custom Column, this message appears:

I tried looking for duplicated customer id in the summarized customer table and sales order id in the sales table, but I don't find any.  

Is there anything wrong with my DAX?  Could the duplicate exist somewhere I haven't looked?

Is there a different DAX I can use to find first order date by customer so to circumvent the INDEX error?

Thanks.

[Ahmed-Elfeel · Accepted solution]

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

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[FBergamaschi · Accepted solution]

H @etane,

to find first order date by customer for Product A you can use this DAX in a calculated column in Customer

First Purchase A Prod =

MINX (

    FILTER (

        Sales,

        Sales[CustomerKey] = Customer[CustomerKey] &&

        Sales[PRODUCTYPE] = "A" && Sales[ORDERSTATUS] = "COMPLETE"

    ),

    Sales[OrderDate]

)

Please try to avoid such a complex DAX code your wrote, it involves a lot of unnecessary steps that make everything more complex than needed

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

assuming PRODUCTYPE and ORDERSTATUS are columns in the Sales table

[ValtteriN · Accepted solution]

Hi, 

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

OrderRank =

RANKX(

    FILTER(

        SALESTABLE,

        SALESTABLE[CUSTOMER_ID] = EARLIER(SALESTABLE[CUSTOMER_ID])&&SALESTABLE[PRODUCTYPE]="A"

    ),

    SALESTABLE[SALESDATE],

    ,

    ASC,Dense

)


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[etane · Accepted solution]

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]

)

[ValtteriN · Accepted solution]

Hi, 

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

OrderRank =

RANKX(

    FILTER(

        SALESTABLE,

        SALESTABLE[CUSTOMER_ID] = EARLIER(SALESTABLE[CUSTOMER_ID])&&SALESTABLE[PRODUCTYPE]="A"

    ),

    SALESTABLE[SALESDATE],

    ,

    ASC,Dense

)


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[FBergamaschi · Accepted solution]

H @etane,

to find first order date by customer for Product A you can use this DAX in a calculated column in Customer

First Purchase A Prod =

MINX (

    FILTER (

        Sales,

        Sales[CustomerKey] = Customer[CustomerKey] &&

        Sales[PRODUCTYPE] = "A" && Sales[ORDERSTATUS] = "COMPLETE"

    ),

    Sales[OrderDate]

)

Please try to avoid such a complex DAX code your wrote, it involves a lot of unnecessary steps that make everything more complex than needed

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

assuming PRODUCTYPE and ORDERSTATUS are columns in the Sales table

[etane]

Sorry for the late reply.  Reason I didn't use min function is because I also need to calculate 2nd, 3rd ... Nth transaction dates.  That's why the index function works... until the error messages:

Without using INDEX, how can I get the Nth transaction date?

[Mauro89]

Hi @etane,

is there a reason why you use DAX for this?

Best practise would be to perform this operation somewhere more in the backend. 

Maybe give it a try using Power Query with M. Set the query on top of your sales table and create a new one with the same logic. 
This deloads also your report during runtime regarding performance. 

Second approach but depending on your backend not as easy to implement is to create the table before it gets loaded in Power BI. E.g. in your warehouse or lakehouse. 

Best regards!

PS: if you find this helpful, leave some kudos or mark it as solution

[etane]

It's the only DAX that I know of (actually provided to me by someone in this forum) that provides me with not just the 1st transaction date but also by the Nth transaction date.

I am not good with M.

[Ahmed-Elfeel · Accepted solution]

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

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[etane]

Thank you for your detailed response.  I think the MAXX(TopN) function can work.  Will try it out first.