OFFSET's Relation parameter may have duplicate rows. This is not allowed.

[tecumseh]

Hi all,

I am trying to partition and sort my calculated table by Customer Group ID and Date. However OFFSET is stating that I may have duplicate rows in the source table. I checked the source table it definitely does not have duplicate rows what else could be the issue? Previous Qtr Date below may not be sequential. A customer may not have Rev in any given quarter so that quarter is filtered out in a previous step. For example, a customer may have revenue in Q12018, and then nothing again until Q22023. Every customer is different.

DAX to Partition and Sort:

"OFFSET's Relation parameter may have duplicate rows. This is not allowed."

Try_2 = 
VAR T10 = 
SELECTCOLUMNS(
Try_1,
"Customer Group ID", Try_1[Customer Group ID],
"Begin Quarter Date", Try_1[Begin Quarter Date],
"Revenue", Try_1[Revenue]
)
VAR T20 = 
ADDCOLUMNS(
T10,
"Previous Qtr Date",
SELECTCOLUMNS(
OFFSET(
-1,
T10,
ORDERBY([Begin Quarter Date]),
PARTITIONBY([Customer Group ID])
),
[Begin Quarter Date]
)
)

RETURN
T20

DAX to Verify Distinct Rows:

Table Try_3 (2,564 rows) Column Cust_Group_ID_Date (2,564 distinct values)

Try_3 = 
VAR T10 = 
SELECTCOLUMNS(
Try_1,
"Customer Group ID", Try_1[Customer Group ID],
"Begin Quarter Date", Try_1[Begin Quarter Date],
"Revenue", Try_1[Revenue]
)
VAR T20 = 
ADDCOLUMNS(
T10,
"AA_ID_Date", [Customer Group ID] & "_" & FORMAT([Begin Quarter Date],"YYYYMMDD")
)

RETURN
T20

[parry2k]

@tecumseh I'm really not clear on what you are looking for, if you don't pass ASC/DESC value to ORDERBY the default is set to ASC, so adding ASC to the ORDERBY will not make a difference, but if you change it to DESC you will get a different value.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[tecumseh]

Thanks @parry2k ,

I guess I expect the data to display in Ascending order, that may not be the case, sometime the the display of the data is in descending order, sometimes the the data is displayed is all over the place, it does not appear to effect the calculation outcome of OFFSET or INDEX Functions.

Thanks

[parry2k]

@tecumseh sorry not clear what is the issue?

[tecumseh]

Thanks @parry2k ,

The data is sorted in DESC order. I need it in ASC order. But even when I add the ASC modifier to the ORDERBY clause,  the data is still in DESC order.

Thanks

[Ahmedx]

read about it here please

https://pbidax.wordpress.com/2022/12/23/introducing-dax-window-functions-part-2/

[parry2k · Accepted solution]

@tecumseh try this:

Try_2 = 
VAR T10 = 
SELECTCOLUMNS(
Try_1,
"Customer Group ID", Try_1[Customer Group ID],
"Begin Quarter Date", Try_1[Begin Quarter Date],
"Revenue", Try_1[Revenue]
)
VAR T20 = 
ADDCOLUMNS(
T10,
"Previous Qtr Date",
SELECTCOLUMNS(
OFFSET(
-1,
T10,
ORDERBY([Begin Quarter Date]),
PARTITIONBY([Customer Group ID])
MATCHBY([Begin Quarter Date])
),
[Begin Quarter Date]
)
)

RETURN
T2

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[tecumseh]

Thanks @parry2k ,

That did get rid of the error: "OFFSET's Relation parameter may have duplicate rows. This is not allowed."
But the table is not sorted by Begin Quarter Date

Here  is a sample customer where I obfuscated the customer group id. Everything else is exactly what I see in the result table in the order I see it.

I tried adding the ASC modifier in the ORDERBY clause, but the result  is exacly the same

ORDERBY([Begin Quarter Date], ASC),

Thanks for your good help.