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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tecumseh
Resolver I
Resolver I

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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