Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tecumseh
Helper II
Helper II

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

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

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

@tecumseh sorry not clear what is the issue?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

offset_sort.PNG

Ahmedx
Super User
Super User
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

offset_sort.PNG

 

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.





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.