0

I have the following data structure:

Scope,Metric ID,Item ID,System,Color
TRUE,A1,123,A,Red
FALSE,A1,123,B,Red
FALSE,B1,234,C,Red
TRUE,B1,234,A,Red
FALSE,B1,415,A,Red

I'd like to group by Scope, filter on TRUE and get the unique list of Items, then count these Items and subtract from a total unique count for the Color = Red.

So, in the example above, I have 3 unique items for Color = Red and I have 2 unique items with Scope = TRUE, so the result should say 3 - 2 = 1.

Because of the data structure, simple filtering won't help. I realize I need to use a complex LOD syntax, but after having tried them for a few hours, I find them rather confusing.

Does anyone have an idea how to write an LOD expression to give me the desired count? Thanks!

minatverma
  • 1,090
  • 13
  • 24
Pavel
  • 53
  • 1
  • 2
  • 8
  • For `color = RED` and `scope = TRUE` there are 2 distinct system. Why do you need 1 ? Can you rephrase your question ? – minatverma Jan 19 '16 at 18:29
  • could you please provide the desired output? I mean, do you need just 1 as result or do you need also a sheet with dimension? – Fabio Fantoni Jan 20 '16 at 08:46

1 Answers1

0

Did you try using 3 calculated fields like this: then doing a count distinct on them.

1:

if [Color]='Red' then [Item ID] end

2:

if [Scope]='TRUE' then [Item ID]        end

3 : 

subtract the 2 calculated fields i,e 2-1

It gives out 1.

vhadalgi
  • 7,027
  • 6
  • 38
  • 67