How to check for a Value in a column while filtering

[Anonymous]

Hi,

         How can I check whether a value exist in a column while filtering the same column??

          For instance, in my current table I have a 'GROUP' column with distinct values (G1 to G8). I have visualized a graph for which all groups are not applicable, created a filter for GROUP. If G1 is the only group which is applicable to the graph, and in filter if I am selecting G1, graph should be shown and if G1 is not selected in the filter, a message 'Graph not relevant' should be shown in the KPI I created near the graph.

         I have created an expression measure for the KPI as shown below which is working for my requirement if I am selecting only one value in the filter.

                               if(GROUP like 'G1','-','Graph not relevant')

         But if I am selecting any more than one value including 'G1', the value passing to KPI is 'Graph not relevant'. Is there any method to check whether the filter contains value 'G1', so that the number of values selected in the filter will not effect the condition check.

        Please suggest, Thanks in advance.

[Gysbert_Wassenaar · Accepted solution]

By filter you mean selection, right? Then you can test with =if(substringcount(getfieldselections(GROUP, ',', 99),'G1'),'Match', 'No match')

[sunny_talwar]

Try this instead:

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1, '-', 'Graph not relevant')

[Anonymous]

Thanks Gysbert for the quick response, appreciated.

Its working fine, but for my understanding what if 'G1', 'G2'(two or more groups) are relevant for the graph, how can we acheive the same for the above logic??

[sunny_talwar]

Add two statements separated by and

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 and

   SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 and

   SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

[sunny_talwar]

And if it is either G1 or G2 condition, then may be this

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 or

  SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

[Anonymous]

Thank you Sunny, it worked for me, taken the implementation logic from your script

Sunny T wrote:

And if it is either G1 or G2 condition, then may be this

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 or

  SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

[Anonymous]

Hi Sunny,

          I have one more doubt coming up, how can I hide the message 'Graph not relevant' if i am not making any selection on the 'GROUP'(as by default all values of the group (G1 - G8) will be part of the dataset) ?

[Anonymous]

Hi,

          I have one more doubt coming up, how can I hide the message 'Graph not relevant' if i am not making any selection on the 'GROUP'(as by default all values of the group (G1 - G8) will be part of the dataset) ?

[sunny_talwar]

May be this:

If(GetSelectedCount(GROUP) = 0, ' ',

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant'))