在单个MDX查询中多次使用时，IIF()函数无法正常工作 [英] IIF() function not working properly while using multiple times in single MDX query

问题描述

我需要使用单个MDX查询根据两个条件获取聚合值

我有以下数据

我在下面的查询中使用IIF()函数创建了两个计算函数

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

输出必须是3693和103，但返回如下

查询中是否需要进行任何更改？

有人能指导我得到正确的结果吗？

推荐答案

您的想法是正确的，但需要跨员工职称执行此计算：

WITH
  MEMBER [Measures].[Expression1] AS 
   SUM(
     [EMPLOYEE].[TITLE].[TITLE].MEMBERS,
     IIF([EMPLOYEE].[TITLE].CURRENTMEMBER IS [EMPLOYEE].[TITLE].[SALES REPRESENTATIVE],[Reseller Order Count],Null)
   )
  MEMBER [Measures].[Expression2] AS 
   SUM(
      [EMPLOYEE].[TITLE].[TITLE].MEMBERS,
      IIF([EMPLOYEE].[TITLE].CURRENTMEMBER IS [EMPLOYEE].[TITLE].[SALES REPRESENTATIVE],Null,[Reseller Order Count])    
    )
SELECT{
        [Measures].[Expression1],
        [Measures].[Expression2]} ON COLUMNS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING 

这篇关于在单个MDX查询中多次使用时，IIF()函数无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！