BAR CHART, exclude variables in the DIMENSION

[#1]

When I make a BAR CHART, I want to include the value "NULL"?

As you can see the value "NONE" and "NULL"  dominates the chart, this should be eliminated, how do I do that?

[Anonymous · Accepted solution]

you can do it in calculated Dimension or in Expression

Dimension:

If (Dim<>'NONE' or Dim<>'NULL',Dim,null())

Expression

sum({<Dim-={'NONE','NULL'}>}Measure

[#3]

Hi Jane

As noted above you can use a calculated dimension or an expression to achieve this.  A key point not mentioned above is that if you use a calculated dimension you must remember to tick the 'Suppress When Value Is Null' option.

To illustrate, you start with:

If you add a calculated dimension =If(Dimension<>'NONE' and Dimension<>'NULL',Dimension,) but forget to tick the 'Suppress When Value Is Null' option you will get:

You can see that the excluded values are now shown as a new untitled bar.  Selecting the calculated dimension and ticking the 'Suppress When Value Is Null' option removes this bar so you end up with:

The option can be found on the Dimensions tab of the chart properties

In the calculated dimension formula it is important to use the correct logical operator.  In the examples given in the previous answer the operator 'OR' is used.  This will not work as effectively the expression is saying if the dimension is not NONE or the dimension is not NULL then display the dimension.  However when it is NULL it is not NONE and when it is NONE it is not NULL so the values will persist.  To get them to disappear you have to basically say only show the values where the dimension is not NULL and NONE, hence the 'and' operator will work. E.g.

=If(Dimension<>'NONE' and Dimension<>'NULL',Dimension,)

Another way to write this which may be easier to follow is =If(Dimension='NONE' or Dimension='NULL',Null(),Dimension)

Note in this version the operator 'OR' is used rather than 'AND'.  What makes the difference is that in the first example the expression is based on NONE and NULL not being present whereas in the second version the expression is based on where they are present.

So to summarise the second formula above basically says if the value of the dimension is NONE or NULL then replace the value with a nulled result otherwise show the value of Dimension.  With the values of NONE and NULL being converted to a nulled result then when the 'Suppress When Value Is Null' option is ticked the values NONE and NULL vanish from the chart.

To use the expression version then just use the Dimension and an expression of

sum({<Dimension-={'NONE','NULL'}>}Value1)

which is basically saying 'only sum the values in the Value1 field where the value in the Dimension is not NULL or NONE.  You don't need to tick the 'Suppress When Value Is Null' option for this expression approach to work.

Hope that helps

Kind regards

Steve

[svenkita]

Attaching is a sample file.

I have created 2 expressions one without NONE & NULL and other one ONLY FOR NONE & NULL

in the Axes tab of properties I have changed the position of one expression on the left and other on the right

Hope this helps

[#5]

thank you!

[#6]

You're most welcome.  Glad it helped 🙂