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
RobertSlattery
Resolver III
Resolver III

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

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

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!

v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors