Blog | Octane Software Solutions

Mastering TM1DISTINCT: The Smart Way to Clean Up Your MDX Subsets

Written by Nomaan Syed | 15 April 2026 12:30:01 AM

IBM TM1 models can get messy fast, especially when you use alternate hierarchies and elements show up in multiple places. That's where the TM1DISTINCT MDX function quietly saves the day. Instead of blindly stripping out anything that "looks" duplicated, it understands TM1's hierarchies and only removes true duplicates – the exact same member in the exact same context

The Problem: Why Regular DISTINCT Isn't Enough

Imagine you're building a dynamic subset of Products. You pull all products under All Products, then union it with a special "Focus Products" consolidation. Now some products appear twice in the raw result. With the classic DISTINCT, TM1 might collapse these duplicates in a way that hides the structure you actually care about.

This is especially problematic when working with:

  • Alternate hierarchies that place elements in multiple logical positions

  • Union operations that naturally generate overlapping sets

  • Complex dimension structures where the same leaf element has different parents

  • Dynamic subsets that combine multiple source sets.

TM1DISTINCT is smarter: it keeps the element where it appears in different meaningful places, and only cleans up genuine duplication caused by unions or repeated logic.

Understanding TM1DISTINCT vs DISTINCT

The key difference lies in context awareness:

  • DISTINCT: Removes any duplicate entries that match another entry based on the member name. This can accidentally collapse elements that appear legitimately in different branches of the hierarchy.

  • TM1DISTINCT: Removes duplicates only when they are truly identical – same element, same hierarchy path, same context. It respects the multi-hierarchical nature of TM1. 

While the existing DISTINCT function removes duplicate elements from a set, the new TM1DISTINCT function removes duplicate members only if they are truly identical, including their parent context. This distinction is important because a single element can appear as multiple members in a hierarchy if the element has different parents.

This distinction becomes critical when your dimension design intentionally places elements in multiple locations for different analytical views.

Practical Examples

Example 1: Basic Leaf-Level Filtering

TM1DISTINCT( 
TM1FILTERBYLEVEL( 
{Descendants([Product].[All Products])}, 
0 
) 
)
Here, you get a clean leaf-level list of products, free of accidental duplication, but still faithful to how the hierarchy is built. The function returns all leaf-level descendants while removing any technical duplicates that might arise from the query logic.

Example 2: Combining Multiple Sets (Union Scenario)

TM1DISTINCT(
{ TM1SubsetAll([Customer]) + [Customer].[Key Accounts] }
)

You end up with each real customer only once, even though "Key Accounts" is already part of the full customer list. This is where TM1DISTINCT truly shines – it preserves your intentional hierarchy structure while cleaning up the noise.

Example 3: Alternate Hierarchy Preservation

TM1DISTINCT(
TM1FILTERBYLEVEL(
{Descendants([Cost Center].[Total Company])},
0
)
)

Leaf-level cost centers under Total Company are returned, and any technical duplicates from unions or repeated selection logic are cleaned up safely. The alternate hierarchy placements remain intact.

Real-World Impact

Consider a retail company with a Product dimension that has both:

  • A Standard Hierarchy: All Products → Category → Subcategory → SKU

  • An Alternate Hierarchy: All Products → Channel → Brand → SKU

The same SKU (say, "Blue Shirt Medium") legitimately appears under both "Subcategory" and "Brand." Using DISTINCT here might collapse one of these occurrences, breaking reporting by channel. Using TM1DISTINCT keeps both occurrences because they represent different analytical contexts. 

Best Practices

  1. Use TM1DISTINCT when building dynamic subsets that combine multiple sets or work with alternate hierarchies

  2. Avoid it only for simple, single-hierarchy subsets where standard DISTINCT would work fine

  3. Combine with TM1FILTERBYLEVEL to ensure clean, context-aware filtering

  4. Test with your actual dimension structure to verify the results match business expectations.

Conclusion

TM1DISTINCT represents a maturation of MDX handling in Planning Analytics, acknowledging that TM1's rich hierarchy support requires intelligent de-duplication. By using it in your dynamic subsets, you ensure clean data without sacrificing the intentional structure your dimensions are built upon. Your business users – and your data model – will thank you for it.

References

  1. IBM Planning Analytics Documentation. (2024). TM1DISTINCT( <set> ). IBM. https://www.ibm.com/docs/en/planning-analytics/3.1.0?topic=tsmf-tm1distinct-set

  2. IBM Planning Analytics. (2024). TM1 specific MDX functions. IBM. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=mfs-tm1-specific-mdx-functions
View full post