Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a customisable straight table that allows users to select dimensions and metrics to create their own straight table. I have encountered a problem where more than one dimension is brought into the table even though only one is selected. This occurs when the dimensions have similar names (the same first word in the name) i.e. 'Disability status' and 'Disability'. When a user selects the dimension 'Disability' both dimensions are shown. The expression I'm using for each of these two dimensions are -
Disability status:
SubStringCount(Concat(_dimension, '|'), 'Disability status')
Disability:
SubStringCount(Concat(_dimension, '|'), 'Disability')
Is there a way to amend the expression to stop this happening?
Thanks
Greg
Or
=SubStringCount('|' & Concat(distinct _Dimension, '|') & '|', '|Disability|')
and
=SubStringCount('|' & Concat(distinct _Dimension, '|') & '|', '|Disability status|')
Try something like this
=SubStringCount('%'&Concat(_Dimension, '%|%')&'%','%Disability%')
and
=SubStringCount('%'&Concat(_Dimension, '%|%')&'%','%Disability status%')
Or
=SubStringCount('|' & Concat(distinct _Dimension, '|') & '|', '|Disability|')
and
=SubStringCount('|' & Concat(distinct _Dimension, '|') & '|', '|Disability status|')
Thanks. This works. Maybe one day I'll understand how.
Hi Greg,
Here is another approach, it works well for me. You can create a data island for your chart dimension. I create this from an Excel sheet, this is my data:
%Dim1 |
Customer Group |
Customer |
Rep |
Product Class |
Product Group |
Product Description |
Thickness |
Condition |
Size |
For me '%' is the prefix that hides selections on a field.
You can see that I have exactly the same issue as you, for the values 'Customer' and 'Customer Group'.
I have a listbox for %Dim1 set to one selected value only that I select my dimension from and my calculated dimension in my chart is:
=$(='[' & %Dim1 & ']')
Kind regards
Andrew