1 Reply Latest reply on Nov 29, 2016 5:12 PM by Shinichiro Murakami

    how to show measures based on select members of a dimension

    Angela Frey

      This is just an idea of how to show measure value based on select members of a dimension.

       

      Note, if using an aggregation other than COUNTD() I would consider duplicating the data source and using Quick Filters, but COUNTD() won't work on blended data.

       

      Specifically, I created three parameters for "excluding" a customer from (Most) and only showing that customer in (Excluded). The following calculations, when placed in the view, will show the measure based on the parameter selection:

       

      [Orders (Most)]:

      COUNTD(

      IF [Exclude Customer 1] = [Customer Name] THEN null

      ELSEIF [Exclude Customer 2] = [Customer Name] THEN null

      ELSEIF [Exclude Customer 3] = [Customer Name] THEN null

      ELSE [Order ID] END)

       

      [Orders (Excluded)]:

      COUNTD(

      IF [Exclude Customer 1] = [Customer Name] THEN [Order ID]

      ELSEIF [Exclude Customer 2] = [Customer Name] THEN [Order ID]

      ELSEIF [Exclude Customer 3] = [Customer Name] THEN [Order ID]

      ELSE Null END)

        • 1. Re: how to show measures based on select members of a dimension
          Shinichiro Murakami

          Hi Angela

           

          You can simplify the formula like below.

           

          [Orders (All) SM]

          COUNTD([Order ID])

           

          [Orders (Excluded) SM]

          COUNTD(

          IF [Exclude Customer 1] = [Customer Name]

          OR  [Exclude Customer 2] = [Customer Name]

          or [Exclude Customer 3] = [Customer Name]

          then  [Order ID] END )

           

          [Orders (Most) SM]

          COUNTD(

          IF [Exclude Customer 1] <> [Customer Name]

          and [Exclude Customer 2] <> [Customer Name]

          and [Exclude Customer 3] <> [Customer Name]

          then  [Order ID] END )

           

           

          Or simplify one step further, maybe this is much simpler and flexible..

           

          [Customer Category]

          IF [Exclude Customer 1] <> [Customer Name]

          and [Exclude Customer 2] <> [Customer Name]

          and [Exclude Customer 3] <> [Customer Name]

          then  "Most" else "Excluded" end

           

           

          Thanks,

          Shin