Best Practices for Handling Many-to-Many Joins in AtScale Semantic Models

Hi everyone,

I’m Mark Pousee, and I’m working through some modeling challenges in AtScale’s Design Center with the Semantic Modeling Language (SML). The specific issue I’m running into is with many-to-many relationships for example, mapping zip codes to multiple sales territories.

Here’s what I’ve tried so far:

• Created a bridge (associative) table in SML to handle the M:M relationship.
• Published the model and tested it in Power BI and Excel.
• The queries technically return, but I’m seeing double-counting and inflated aggregations when rolling up measures.
• Tried adjusting the measure definitions with distinct_count and custom aggregation logic, but the results are still inconsistent.

A few specific questions I’m hoping the community can help with:

1. Is there a recommended pattern in SML for many-to-many joins (e.g., explicit bridge modeling vs. virtual cube logic)?
2. How do you ensure that end-users only see the clean dimension/measure set, while AtScale handles the complexity under the hood?
3. Is there a query rewrite or aggregate design best practice that avoids performance hits when working with these bridges?
4. Any sample SML snippets for how you’ve handled similar cases would be amazing.

My end goal is to make sure that a BI user in Power BI or Excel doesn’t even realize a bridge table exists, they just drag in “Territory” and “Sales” and get clean, non-duplicated results.

Would love to hear how others in the community have solved this.

Thanks in advance,
Mark Pousee