Selecting only a portion of data from a dimension

[Amen90]

Hi Everyone,

I have a Dimension that I want to pivot but I only want to select one part of the dimension to pivot rather the whole table, and I want the dimension to change based on a field selection filter .  So in the dimension I have two sub parts called 'new' and 'old'. I would like the table/subsequent chart to only show  'new', and then I want to do a separate chart that will only show 'old'. For example, I currently have in  my expression field (which works well!);

if(GetFieldSelections(Category)='A', [Apples],

if(GetFieldSelections(Category)= 'B', [Oranges],

if(GetFieldSelections(Category)= 'C', [Pears],

if(GetFieldSelections(Category)= 'D', [Cherries],

if(GetFieldSelections(Category)= 'E', [Bananas],0)))))

How can I edit the expression above so that the expression  only selects the 'new' segment within each dimension, when the categories filter is changed to its corresponding data pair .

Kind regards,

[Sebastiaandb]

Hey Armen, is the 'new' and 'old' tag part of the same dimension as your example in that it is the same variable or is it a different variable? Would be awesome if you could give us .qvf or some more insight in your variables :-).

[Amen90]

Hi Sebastiaan,

thanks for replying! the new and old tags are a part of dimension. So;

[Apples] > includes a 'new' and 'old' category

[Oranges] > includes a 'new and 'old' category

[Pears]> includes a 'new and 'old' category

[Cherries]> includes a 'new and 'old' category

[Bananas]> includes a 'new and 'old' category

Unfortunately I cant share the qvf file, I've had the mask the actual dimensions I'm working with, apologies!

Kind regards

[Sebastiaandb]

He Amen,

I'm not the most knowledgeable guy around here but could you make a simple table like:

This would be usefull to find a solution. From what i hear it would be very convenient for you to have the variable extension.

Category	Fruit	New/old
A	Oranges	New
A	Oranges	Old
B	Bananas	New
B	Bananas	Old

[Amen90]

thank you for the suggestion but my table is more like;

Category	Apples	Measure
A	New	1
A	Old	2

So I would like my  expression to select 'new' in the dimension (apples) and display that within a table, omitting the 'old'

kind regards,

[Sebastiaandb]

Not sure if this does the trick for you but try this as a dimension:

=if(Category='A' and Apples = 'New' ,
[Apples],
if(Category= 'B' and Oranges = 'New',
[Oranges]))
(extend it for the rest of your fruit dimensions)

Deselect 'show null values'

EDIT

Might be easier to change the expression for your measure to

if(Category = 'A',
Sum({<
Apples = {'New'}
>}
Measure),
if(Category = 'B',
Sum({<
Oranges = {'New'}
>}
Measure)))

[Sebastiaandb · Accepted solution]

Is your problem solved?