SUMMARIZECOLUMNS / data reduces when adding a field

Thankyou, OwenAuger, for your response.

Hi Power_It_Up,

We sincerely appreciate your inquiry via the Microsoft Fabric Community Forum.

From my understanding, your observation regarding the behavior of SUMMARIZECOLUMNS resembling an inner join in certain contexts is indeed correct. When you include a column from your ninth table and notice a reduction in the number of rows, this may be attributed to the following reasons:

1.There are no matching keys between your base tables and the new table for some or all rows.
2.SUMMARIZECOLUMNS returns only combinations where all grouping columns exist, i.e., it applies auto-exist filtering.
3.It excludes rows where all expressions return blank, unless explicitly overridden using the IGNORE keyword.

To include unmatched data, kindly consider the following option which may help resolve the issue:

Use ADDCOLUMNS in combination with RELATED, which provides a left join effect by preserving unmatched rows:
ADDCOLUMNS (

    SUMMARIZE ( 'BaseTable', 'BaseTable'[Field1], 'BaseTable'[Field2] ),

    "RelatedField", RELATED ( '9thTable'[FieldX] )

)

Here, SUMMARIZE generates a base list of values, and RELATED safely retrieves matching values, returning blank when no match is found, thereby preserving the rows.

If the issue persists, please share sample data that clearly illustrates your problem or query in a usable format (not as a screenshot). Kindly ensure that sensitive or unrelated information is excluded. Additionally, please specify the expected outcome based on the provided sample data.

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will benefit other community members seeking similar assistance.Should you have any further questions, please feel free to contact the Microsoft Fabric Community.

Thank you.