SUMMARIZECOLUMNS / data reduces when adding a field

[Power_It_Up]

Hi. 

I'm using SUMMARIZECOLUMNS to create a table with just the fields I need. I have a schema of eight tables and all are many-to-one.

I have added a ninth table, same relationship, but when I add a field from this 9th table to SUMMARIZECOLUMNS, the data is significantly reduced. I don't understand why as there should be many matches between the tables I am joining. 

I believe SUMMARIZECOLUMNS acts as an inner join. Is there a way to include unmatched data? I've tried using ALL(), but doesn't seem to work. 

Thanks. 

[Power_It_Up]

@OwenAuger @v-pnaroju-msft Thank you both very much for your direction and my sincere apologies for not replying sooner.  I used the code provided and tweaked it a little to suit my needs.  Thanks again! 🙂

[v-pnaroju-msft]

Hi Power_It_Up,

We wanted to follow up since we have not heard anything from you about your query. If you fixed the issue, please share your solution here. It will be useful for others with similar problems.

If our response helped you, kindly mark it as accepted solution to help other members.

Thank you.

[v-pnaroju-msft]

Hi Power_It_Up,

We are waiting for your reply regarding the query. Kindly tell us if you have found the solution. If yes, please share it with the community to help others with the same problem.

If you found the reply useful, please mark it as accepted solution so other people can see it easily.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[OwenAuger]

Hi @Power_It_Up 

With SUMMARIZECOLUMNS, "group by" columns from the same table are "auto-existed" with each other, meaning only existing combinations are included in the result. Then the sets of columns from each table are cross-joined with each other.

If at least one expression is included, by default only rows where at least one expression is nonblank are returned. In other words, rows where all expressions are blank are removed (but this can be modified with IGNORE).

Could you share the complete SUMMARIZECOLUMNS expression and the outcome you were wanting, with some sample data or model diagram if possible?