3

I need to get calculated measure values based on conditions.

Am having the below data in my cube enter image description here

I have created below calculated measure using IIF() function

IF([Title] = "Sales Representative",[Reseller Order Count],0)

I have tried with below query:

WITH
MEMBER [Measures].[Expression1] AS IIF([EMPLOYEE].[TITLE].CURRENTMEMBER.MEMBERVALUE  = "SALES REPRESENTATIVE",[Reseller Order Count],0) SELECT{
[Measures].[Expression1]} ON COLUMNS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

It returns 0 but the value returned must be 3693 enter image description here

But while using dimension field in the above query, it returns value for that particular Title and remaining Titles has 0.

WITH
         MEMBER [Measures].[Expression1] AS IIF([EMPLOYEE].[TITLE].CURRENTMEMBER.MEMBERVALUE  = "SALES REPRESENTATIVE",[Reseller Order Count],0)    SELECT{
        [Measures].[Expression1]} ON COLUMNS ,NONEMPTY((TOPCOUNT(
        (Order((([Employee].[Title].[Title].ALLMEMBERS)),[Employee].[Title].CurrentMember.MemberValue,ASC)),101)),{
        [Measures].[Expression1]}) ON ROWS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

enter image description here

Is there any way to get the result without using dimension field in the MDX query?

I have checked multiple solutions in StackOverFlow and tried but none has achieved my requirement. Can anyone please guide me to get the required value?

TAMILARASAN R
  • 225
  • 1
  • 6
  • 3
    Does this answer your question? [IIF() function not working properly while using multiple times in single MDX query](https://stackoverflow.com/questions/70822913/iif-function-not-working-properly-while-using-multiple-times-in-single-mdx-que) – GregGalloway Jan 24 '22 at 06:50

0 Answers0