I am sure that this technique has been covered before, but I have recently came across it and I thought it would be good to share with fellow TM1ners
Working on the model recently I found myself in need of MDX script that would combine TOP5 customers based on their sales for each state i.e TOP5 for NSW, QLD,VIC and so on in one dimension. Basically customer wanted to see TOP 5 sellers for each state in one active form column.
After scratching my head for a bit I decided to play around with Union function. The trick is to use Nested Unions as per example below:
{UNION(FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )}, 0)}, [Products].[State] = "NSW"),
UNION(FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )}, 0)}, [Products].[State] = "VIC"),
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )}, 0)}, [Products].[State] = "QLD") ) ) } }
As you know UNION takes 2 sets of data, therefore its possible to replace second set with another Union and so on for as many parameters as you need.
This example can be simply extended to use TOPCOUNT by nesting it before FILTER({...
It definitely worked for me
!Thanks
Evgeny