Search code examples
sqlmdxbusiness-intelligencecube

How do I exclude columns which start with a specific string in MDX?

I'm trying to write an MDX query with the equivalent SQL:

SELECT m.ID, m.CID, m.Orders
FROM dbo.Measures as m
WHERE SUBSTRING(m.CID, 1, 4) <> 'PID_'

Essentially, exclude all rows where CID begins with 'PID_'

This is what I have in MDX so far:

SELECT 
{
    [Measures].[ID] AS ID,
    [Measures].[Orders] AS NumberOfOrders,
}
ON COLUMNS, 
{
    [Channel].[Channel Account ID].[Channel Account ID].Members
    * [Channel].[Channel].[Channel].Members // exclude accounts starting with 'PID_'
}

I've tried EXCEPT and - and WHERE clauses, but none seem to work.

Any help is appreciated!

Solution

  • I found the answer with the links xQbert provided.

    This was the answer:

    ON COLUMNS, 
{
    FILTER([Channel].[Channel Account ID].[Channel Account ID].Members, 
        LEFT([Channel].[Channel Account].Properties("Channel Account ID"), 4) 
            <> "PID_")
    * [Channel].[Channel].[Channel].Members
}