ssasmdxiifmdx-query

Multiple dimensions conditions in SSAS MDX query not working

I Am having the following data in my SSAS cube.

enter image description here

My need is to get the value of the measure based on two conditions with two different dimensions using the MDX.

In this example data, I need to get the Reseller Sales Amount value where the value of Title dimension is equal to Sales Representative and the value of the Genderdimension is equal to Male condition.

I have tried to achieve the requirement with the Case statement and IIF() function available in the MDX but it is not working.

Please find the queries I have tried with different functions.

Using Case statement:

WITH MEMBER [Measures].[Expression1] AS
CASE WHEN [Employee].[Title].CURRENTMEMBER.MEMBERVALUE = "Sales Representative" THEN (CASE
WHEN [Employee].[Gender].CURRENTMEMBER.MEMBERVALUE = "Male"
THEN [Measures].[Reseller Sales Amount] ELSE 0 END)ELSE 0 END select [Measures].[Expression1] on Columns from [Sales Targets]

Using IIF() function:

WITH MEMBER [Measures].[Expression1] AS
IIF( [Employee].[Title].CURRENTMEMBER.MEMBERVALUE = "Sales Representative" AND [Employee].[Gender].CURRENTMEMBER.MEMBERVALUE = "Male",
[Measures].[Reseller Sales Amount], 0)
SELECT{
[Measures].[Expression1]} ON COLUMNS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

The result that I am getting for both queries is 0.

enter image description here

Can anyone please guide me to get the proper result using MDX?

Solution

  • You can use a Tuple either directly in the calculated measure:

    WITH MEMBER [Measures].[Expression1] AS
([Measures].[Reseller Sales Amount], [Employee].[Title].[Sales Representative], [Employee].[Gender].[Male])
select [Measures].[Expression1] on Columns 
from [Sales Targets]

    Or you can put a Tuple in the where clause if you want it to apply to all cells in the query:

    Select [Measures].[Reseller Sales Amount] on Columns 
from [Sales Targets]
where ([Employee].[Title].[Sales Representative], [Employee].[Gender].[Male])