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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RobertSlattery
Responsive Resident
Responsive Resident

Confusing behaviour of OFFSET function

I finally figured out (or at least I think I did) that the preservation of the data lineage of the output row is the issue. The measure running in the OFFSET filter context, will reference the output row from OFFSET, not the input row. This means that, if Dimensions are used for PARTITIONBY and ORDERBY (which the bloody-well should be IMO), only measures, or columns in the Fact where the value for the input and output are the same, will return sensible values in the visual.

 

For example, using this CI in Adventure Works sample pbix...

 

I have a Calculation Item 'Offset measure'[Series] = "Customer Orders"

 

 

CALCULATE (
    SELECTEDMEASURE (),
    OFFSET (
        -1,
        SUMMARIZE (
            ALLSELECTED ( 'Sales' ),
            'Customer'[Customer ID],
            'Sales Order'[Sales Order]
        ),
        ORDERBY ( 'Sales Order'[Sales Order], ASC ),
        PARTITIONBY ( 'Customer'[Customer ID] )
    )
)

 

 

 

Then apply the CI...

 

 

Prev Order := CALCULATE (
    [Selected Value Sales Order],
    'Offset measure'[Series] = "Customer Orders"
)
Prev  Order Sales := CALCULATE (
    [Sum of Sales Amount],
    'Offset measure'[Series] = "Customer Orders"
)

 

 

 

The measure evaluated in the OFFSET context, only shows in a cross-joined row...

 

RobertSlattery_2-1672989384064.png

This means if there are any physical columns included from the Fact table (where the value is different between the original and offset rows), it will be auto-existed in the visual.

 

Please let me know if I am missing something here.

 

 

1 ACCEPTED SOLUTION
AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

Well, the short answer is "yes" 🙂

Window functions return a row in their source table. The external context is used only to determine what the current row in the source table is. Consequently, their lineage is that of the source table.

If you mix in the matrix fact and dim columns, it just becomes a mess. Add to it that ALLSELECTED is nearly always there, it is the perfect recipe for some hours spent trying to understand what is happening with the filter context.

Keep in mind: window functions are designed to be powerful, not simple. They require some getting used to.

Anyway, thanks for the question, it clearly shows you have a sound understanding of the DAX principles, I love these questions when I see them.

Alberto Ferrari - SQLBI

View solution in original post

4 REPLIES 4
AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

Well, the short answer is "yes" 🙂

Window functions return a row in their source table. The external context is used only to determine what the current row in the source table is. Consequently, their lineage is that of the source table.

If you mix in the matrix fact and dim columns, it just becomes a mess. Add to it that ALLSELECTED is nearly always there, it is the perfect recipe for some hours spent trying to understand what is happening with the filter context.

Keep in mind: window functions are designed to be powerful, not simple. They require some getting used to.

Anyway, thanks for the question, it clearly shows you have a sound understanding of the DAX principles, I love these questions when I see them.

Alberto Ferrari - SQLBI

Thanks for confirming Alberto, it helps a lot to have your perspective.   I hope that this behaviour is discussed in the community because, it seems to me, it is not a difficult one to stumble upon so, people probably need to be aware of it.

Certainly my knowledge of DAX principles would be a lot less without the content shared by yourself and Marco and your team.  We are all very grateful!

Anonymous
Not applicable

Hi @RobertSlattery ,

You can review the following video to get a better understanding on the function OFFEST.

NEW OFFSET Function in DAX

yingyinr_0-1673251952405.png

yingyinr_1-1673252047495.png

If the above one can't help you, could you please provide more raw data in 'Customer' and 'Sales Order' table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship created between 'Customer' and 'Sales Order' table? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Hello, the code I listed is for the standard Adventure Works test report but, I added a link to a working example. I had watched the video you linked and like all of the ones I have seen to date, is a trivial example that does not meet this problem. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors