Use the key as dimension but show values from other fields

[Anonymous]

I have an ID filed, which I want to use as dimension, but what should be displayed is the value of other fields on a condition.

So, let's say the data is:

     ID, type, label 1, label 2, measure

I want measure to be for the ID, but what should be displayed in chart as dimension, is 'label 1', if type='A', or 'label 2' if type='B', and the ID when the type filed is empty.

[#2]

Dimension: ID

Expression:

if(type='A',label 1,

if(type='B',label 2,'Empty'))

[swuehl · Accepted solution]

If you want to group your dimension values by ID, but show labels where available (multiple times same label if the label is the same for distinct IDs), you can do it like this, using a calculated dimension (and for numeric IDs)

=Dual(Pick(Match(type,'A','B')+1, ID,[label 1], [label 2]),  ID)

If you want to group per label, just use a calculated dimension (or a new field created in the script), like:

=Pick(Match(type,'A','B')+1, ID, [label 1], [label 2])

[Anonymous]

Thanks. In fact I need both, but I have the following problem with the second:

There are three tables:

table1: ID, label 1, group

and

table2: ID, label 2, group

and

table3: ID, type, year, measure,

I create master dimension Item = Dual(Pick(Match(type,'A','B')+1, ID,[label 1], [label 2]),  ID),

and then I create another master dimension Group = Pick(Match(type,'A','B')+1, ID, [table1.group], [table2.group]).

Then I create a Treemap with dimensions Group, then Item, and measure Sum([measure]).

The problem is, when I add a filter by Group, it doesn't affect the treemap chart.

I guess I shouldn't create Group this way.