OFFSET Function materialises extra rows in Table and Matrix Visuals

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

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.

[v-yetao1-msft]

Hi @RobertSlattery 

I am so glad to hear that you have figured out your issue . Thanks for your sharing . If you have other questions, you can keep posting in the Issue forum for help.

Best Regards,
Community Support Team _ Ailsa Tao

[RobertSlattery]

Hi @v-yetao1-msft Sorry, i didn't word it very well.  I would actually like to know if this is a bug or if there is a way to work arround it.  This behaviour is definitely not desirable.