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

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

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