1

I have a simple sample data set:

Name    Database    Amount
Brian   DC1          50
Brian   DC2          100
Steve   DC1          34
Bill    DC2          90
Ed      DC1          49
Suz     DC2          82

I'm struggling to create a calc or formula to combine "DC1" and "DC2" so that it says both. I'd like to ultimately be able to filter on "DC1", "DC2", or "Both"

Pushing this logic down to the to a sql case statement isn't feasible.

Is this possible to perform? Should I be creating sets and then using combined sets?

tyvich
  • 570
  • 3
  • 13
Seef
  • 35
  • 1
  • 5
  • Can you provide some more info on what you mean exactly by combine? What does your calculated field look like. For Brian do you want it to return the sum of the amount field, 150? – tyvich Nov 02 '17 at 21:50
  • 1
    Hi tyvich! thanks for responding. I'd like to keep the amounts separate. Ideally, I'd be able to filter to see who has DC1, who has DC2, and who has Both DC1 and DC2 while showing the breakouts. A filter selection would be DC1, DC2, and Both. When both is selected, only Brian would show with the corresponding values. – Seef Nov 03 '17 at 01:21

1 Answers1

1

Create a level-of-detail calculated field to see how many distinct database names each account has. Your code will look something like this.

if {FIXED [Name] : countd([database])} > 0 then 'Both' else [Database] END

Sam M
  • 4,136
  • 4
  • 29
  • 42
  • Ahh! Thank you so much! I was working on an LOD calc, but was not nesting it within an if function....that was the missing piece! – Seef Nov 03 '17 at 05:48