Confusing behaviour of OFFSET function

[RobertSlattery]

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...

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.

[AlbertoFerrari · 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.

[RobertSlattery]

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]

Hi @RobertSlattery ,

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

NEW OFFSET Function in DAX

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

[RobertSlattery]

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.