Checking Multiple condition in case statement using MDX query

7.2k views Asked by At

How to write the below case statement in mdx.I moved the below mentioned columns in the fact table.

   sum(case when IsSummary = 1 and IsAudited  = 1 and FinalAuditFlag = 1 then 1 
else 0 end) AuditsCompleted,--Count - Completed Audits

i tried the below MDX query.

WITH 

    MEMBER [Measures].[count]
    AS
    (
     case  ([Measures].currentmember )
            when 0 then ([Measures].[Is Summary] )
                    else 1 
            end 
        )
    Select 
            {[Measures].[count]}    on 0,
            [Dim_table1].[TableId].members  on 1
    from    [Dsv]   
2

There are 2 answers

0
whytheq On

What you have done is almost correct. Just change .currentmember to an actual measure in your cube. Currently when you have the following it is referring to itself i.e. currentmember by the black arrow it referring to the measure count by the black arrow...

enter image description here

This is in AdvWrks:

SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
 ,{[Product].[Product Categories].[Category]} ON 1
FROM [Adventure Works];

It returns this:

enter image description here

If I want to replace the empty cell for Components the we can use CASE:

WITH 
  MEMBER [Measures].[count] AS  
     CASE  [Measures].[Internet Sales Amount]
        WHEN 0 THEN "XXX"             
          ELSE [Measures].[Internet Sales Amount] 
      END  ,format_string = "#,###,##0"
SELECT 
  {
   [Measures].[count]
  } ON 0
 ,{[Product].[Product Categories].[Category]} ON 1
FROM [Adventure Works];

This now returns this:

enter image description here

IIF is used a lot more in MDX than CASE - IIF is nearly always faster. So the above equivalent using IIF is the following:

WITH 
  MEMBER [Measures].[count] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,"XXX"
     ,[Measures].[Internet Sales Amount]
    ) 
   ,format_string = "#,###,##0" 
SELECT 
  {[Measures].[count]} ON 0
 ,{[Product].[Product Categories].[Category]} ON 1
FROM [Adventure Works];
0
SouravA On

I am assuming that your requirement is to find the sum over all the members of [Dim Table1].[TableID] hierarchy.

If so, then the below should work for you:-

WITH MEMBER Measures.[Count] AS
SUM(
    [Dim_table1].[TableId].CHILDREN, 
    [Measures].[Is Summary] * [Measures].[Is Audited] * [Measures].[Final Audited Flag] //If any is 0, it's 0, else 1
   )

SELECT Measures.[Count]
FROM [Dsv] 

Let me know if further slicers are needed to be added or if my understanding is incorrect.