Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use the key as dimension but show values from other fields

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

3 Replies
Not applicable
Author

Dimension: ID

Expression:

if(type='A',label 1,

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

swuehl
MVP
MVP

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
Not applicable
Author

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.