1 Reply Latest reply on Oct 8, 2018 6:38 AM by NeedHelpThanks

    Conditional Counting with MDX

    Felipe Araujo

      Hi everyone,

       

      So, I'm working with a multidimensional database and i need to count how many Employees or rows there are, based on two conditions:

      1: The "STATUS" must be "ACTIVE"

      2: The "QUANTITY SOLD" must be different from zero.

       

      EMPLOYEE_CODE     STATUS          QUANTITY_SOLD

      001                           ACTIVE               0

      002                           INACTIVE           20

      003                           NA                       5

      004                           ACTIVE               10

      005                           INACTIVE           13

      006                           ACTIVE               2

      007                           INACTIVE           10

       

      In the example above, I would like MDX to return "2" (from employees 004 and 006).

      Any ideas on how to solve it?

        • 1. Re: Conditional Counting with MDX
          NeedHelpThanks

          Hi Felipe,

           

          maybe try the following:

           

          Create a calculated member with:

          IIF([QUANTITY_SOLD].currentMember.name > 0 AND

               [STATUS].currentMember.name = "ACTIVE", 1,)

           

          And then sum the result?

           

          You could also try this:

          SUM(IIF([QUANTITY_SOLD].currentMember.name > 0 AND

              [STATUS].currentMember.name = "ACTIVE", 1,))

           

          Best regards,

           

          Moritz