Calculated Dimension Set analysis select few values

[LearningV]

Hi 

I would like to add in a calculated field or use set analysis to ensure only value1, value3,value4,value6 are available or shown in the dropdown list (proposed list below). 

Currently the dimension values 1 - 7 are being shown and are being used for a drop down filter and to show values in the pivot table (current list). 

CURRENT LIST	PROPOSED LIST
Dim1	Dim2
Value1	Value1
Value2	Value3
Value3	Value4
Value4	Value6
Value5
Value6
Value7
Value8
Value9
Value10

I would have included a where statement in the load editor but, all the data needs to be loaded as other sheets rely on the data. 

I have tried to use calculated dimension below but it does not seem to work e.g.

=if(WildMatch(Dim1,'*Value1*','*Value3*','*Value4*','*Value6*'),'Value1','Value3','Value4','Value6')

However when I use only two values it in the calculation it seems to work.

=if(WildMatch(Dim1,'*Value1*','*Value3*'),'Value1','Value3')

Is there a way to fix this and only show a few dimensions and not all using an if statement, set analysis or any type of formulas? 

Thanks

[Kushal_Chawda]

@LearningV  If you have measure in your chart then you can use set analysis to display only specific dimension values

Let's say if your measure is "Sum(Value)" then you can use below set

=Sum({<Dim1={'Value1','Value3','Value4','Value6'}>}Value)

You may need to add this set in all the measure of your chart.

Also, go to chart properties and add-on-> uncheck "Include zero values" option

[LearningV]

Hi @Kushal_Chawda 

I am not sure if that approach works for me? 

As I have a measure called Views. Using your example 

=Sum({<Dim1={'Value1','Value3','Value4','Value6'}>}Views) the value returned is a sum of views.

Using your approach for example the visits just get summed into one cell. 

Currently my data is in a pivot type table. All the data is available and I would like to apply a filter on Dim1 to give me the result in Dim2?   

CURRENT LIST	PROPOSED LIST	Dimensions
Dim1	Dim2	Views	Visits
Value1	Value1	10	57
Value2	Value3	20	45
Value3	Value4	30	43
Value4	Value6	40	43
Value5
Value6
Value7
Value8
Value9
Value10

[LearningV]

I though I may be able to create a new calculated field for Dim1, so for example, if Dim1= Value1 then Value1, if Dim1 = Value3 then Value3 else null.  

If I try to use this method with anymore than two values it does not seem to work 😅

[Kushal_Chawda]

@LearningV  what do you mean by this?

"Using your approach for example the visits just get summed into one cell"

Can you share sample data with expected output?

[LearningV]

Hi @Kushal_Chawda 

Thanks for the quick response.

Here is an example of the current view and expected output. I have listed Views and Visits as the measures for my example. I would like to apply a filter on the dimension1 to only give me certain values. 

CURRENT VIEW		EXPECTED VIEW
Dimension1	Views	Visits		Dimension1	Views	Visits
Value1	10	57		Value1	10	57
Value2	5	50		Value3	20	45
Value3	20	45		Value4	30	43
Value4	30	43		Value6	40	43
Value5	25	20
Value6	40	43
Value7	80	50
Value8	70	40
Value9	90	35
Value10	40	30

Using the formula you suggested =Sum({<Dimension1={'Value1','Value3','Value4','Value6'}>}Views) 

I get something similar to the below. A sum of the values I need, which would normally be fine, but I need them to be listed out against there values and not as a total sum. 

Dimension1	Views
Value1	100
Value3
Value4
Value6

I have experimented with another calculation in the calculated Dimension section Example below

=if(WildMatch(Dim1,'*Value1*','*Value3*'),'Value1','Value3')

And it changed from the current view to the calculated view. This is what I am trying to achieve however when I try and add more Values e.g. 4 and 6, the formula no longer works.  

So is there some sort of if statement or formula that would allow me to do this?

CURRENT VIEW		Calculated VIEW
Dimension1	Views	Visits		Dimension1	Views	Visits
Value1	10	57		Value1	10	57
Value2	5	50		Value3	20	45
Value3	20	45
Value4	30	43
Value5	25	20
Value6	40	43
Value7	80	50
Value8	70	40
Value9	90	35
Value10	40	30

Would have been easier to apply these filters in the load editor but we need all the data to load for other sheets. 

Thank you!

[Kushal_Chawda]

@LearningV  Not sure but I am getting the results based on your data. I have created simple table chart.