topic Re: Does SUMMARIZE Preserve data lineage? in DAX Commands and Tips

Does SUMMARIZE Preserve data lineage?

RobertSlattery — Sat, 05 Nov 2022 05:15:59 GMT

Hi, I can create a virtual table using SUMMARIZE and use it as a filter in a measure...

VAR __types = SUMMARIZE ( 'Item Types', 'Item Types'[Item type] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Items'[Item Code] ), __types )

there is a one to many relationship from Item Types to Items.

When I filter Item Type, the result of the measure is filtered and is equal to the correct value for the number of Items with that type.

To me, this means that Data Lineage is preserved.

But if I try to use RELATED...

VAR __items = SUMMARIZE ( 'Items', 'Items'[Item Code] ) RETURN COUNTX ( __items, RELATED ( 'Item Types'[Item type] ) )

I get an error

The column 'Item Types[Item type]' either doesn't exist or doesn't have a relationship to any table available in the current context.

If I do this, it works fine...

COUNTX ( 'Items', RELATED ( 'Item Types'[Item type] ) )

To me, this means RELATED is broken. What is the logic here please?

I guess it means that the DL is preserved but, only in the direction of the filters (the subsequent DL) so, the sort of "reverse lookup" of RELATED does not work.

Re: Does SUMMARIZE Preserve data lineage?

daXtreme — Sat, 05 Nov 2022 09:21:48 GMT

Hi RobertSlattery

It's not broken. You are all always too quick to jump to (wrong) conclusions 🙂 Have a look at the documentation of the RELATED function: RELATED – DAX Guide

And take a look at the examples. To use RELATED you have to have a row context on a real table in the model. The problem you've got is that SUMMARIZE can return many rows from the base table (that have the same value(s) of the summarized column), not just one that is required for RELATED to work.

Re: Does SUMMARIZE Preserve data lineage?

AntrikshSharma — Sat, 05 Nov 2022 12:25:45 GMT

RobertSlattery RELATED function only works on tables that can be expanded(Table Expansion/Expanded tables) from many to one side , when you use SUMMARIZE to Group BY the data lineage is still there but the new table isn't connected through a physical relationship to other tables due to which it can't expand to other tables.

Re: Does SUMMARIZE Preserve data lineage?

RobertSlattery — Sat, 05 Nov 2022 13:17:12 GMT

Makes sense. Thanks.

So, even if the relationship to the other table is on one of the columns included in the SUMMARIZE?