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)
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
