Search code examples
ssasmdx

How to implement "greater than" based on dimension value in MDX


I would like to query the total sales of each country with the GDP growth rate of more than 3. So far, I can display the sales information of each country like such:

SELECT 
{[Measures].[Sales]} ON 0,
{[Dim Company Info].[LOC].ALLMEMBERS} ON 1
FROM [Database]

But then I am still blank on how to query all GDP growth rate of more than 3. I have searched SO and found filter to be the answer, however, I do not know where to include it in my code above. How do I go about this?

Edit: I have tried the followings, but I do not think that is what I am supposed to do:

WHERE ([Dim Company Info].[Gdp Growth].&[3] : [Dim Company Info].[Gdp Growth].&[3].LastChild)

and

SELECT 
{[Measures].[SALES]} ON 0,
{FILTER([Dim Company Info].[LOC].MEMBERS, [Dim Company Info].[Gdp Growth] > 3)} ON 1
FROM [766 Database]

and

SELECT 
{[Measures].[SALES]} ON COLUMNS,
{[Dim Company Info].[LOC].MEMBERS} ON ROWS
FROM [766 Database]
WHERE FILTER([Dim Company Info].[Gdp Growth], [Dim Company Info].[Gdp Growth] > 2)

Solution

  • You can use your below expression when the dimension members are numericly arranged

    WHERE ([Dim Company Info].[Gdp Growth].&3 : [Dim Company Info].[Gdp Growth].&3.LastChild) with a slight correction

    WHERE ([Dim Company Info].[Gdp Growth].&[3] : [Dim Company Info].[Gdp Growth].[Gdp Growth].LastChild)
    

    However if that is not the case, then you need to follow the below method.

    I am reporting the product with the max quantity.

    select {[Measures].[Internet Sales Amount]} on 0,
    non empty
    ([Product].[Product].[Product],[Promotion].[Max Quantity].[Max Quantity]) on 1 
    from [Adventure Works]
    

    enter image description here

    Now lets manipulate the max quantity to make it behave like a measure.

    with 
    member measures.test
    as 
    case when [Measures].[Internet Sales Amount]>0 then 
    (nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).name
    else 
    null end
    
    select
    {[Measures].[Internet Sales Amount],measures.test}
    on 0,
    non empty ([Product].[Product].[Product] )
    on 1 
    from [Adventure Works]
    

    enter image description here

    Now you can use the filter, but we need to cast(note the use of CInt) the data too

    with 
    member measures.test
    as 
    case when [Measures].[Internet Sales Amount]>0 then 
    Cint((nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).name)
    else 
    null end
    
    select
    {[Measures].[Internet Sales Amount],measures.test}
    on 0,
    non empty (
    filter(
    [Product].[Product].[Product]
    ,measures.test>0) 
    )
    on 1 
    from [Adventure Works]
    

    enter image description here