Independent Listbox without Alternate State

[#1]

Hey,

I have a list box where its fields are dependent on the selection of another listbox.

In that listbox I want to display only sector specific measures dependent on the selection I made under sector. Each measure is assigned to its sector.

When I use this formula to filter my field and make a selection the listbox of sectors will also be affected. Is it possible to use the measure listbox independently except of using alternate states?

=if(GetFieldSelections(sector, ',', 10000, '$')='health',If(match([_Metricsector],'All','health'),_Metric,null()),
if(GetFieldSelections(sector, ',', 10000, '$')='industry',If(match([_Metricsector],'All','industry'),_Metric,null()),
if(GetFieldSelections(sector, ',', 10000, '$')='financial',If(match([_Metricsector],'All','financial'),_Metric,null()),null())))

Measure assignment is as follows:

LOAD * INLINE [
    _Metric, _Metriksector
    KPI1, All
    KPI2, health
    KPI3, industry
    KPI4, financial
];

[#2]

I have provided an example above.

Maybe it does, however, I will try to explain the difference of sector and _Metricsector:

sector: A sector like financial, health, etc. is assigned to any company in the overall data model.

_Metricsector: This only gives each KPI the property for which sector it is valid. Some are only valid for certain sectors, some are valid cross-sectorally.

So depending on the sector of the company the valid KPIs will be displayed. If a KPI is valid cross-sectorally it will be displayed whatever the sector of the company is. Be aware there is no sector "All", it could also be "tomato". It is just a place holder for the purpose of filtering basically.

It is quite tough to explain, I hope it became clear.

[Digvijay_Singh · Accepted solution]

I could recreate your problem. See the attached updated sample, if you select KPI, it won't impact sector now.

[#4]

Ok one more question regarding this:

When I use this expression:

=SubStringCount(Concat(_Metric, '|'), 'KPI2')

It shows a one anyway, whether it is currently displayed in the listbox or not.

Can I reduce the StringCount to the filtered _Metric Listbox instead of the complete values of _Metric?

[Digvijay_Singh]

If you use the same expression here, it works, I tried as -

=SubStringCount(Concat(

  Aggr(Only({1}if(GetFieldSelections(Sector, ',', 10000, '$')='Health',If(match([_Metricsector],'All','Health'),_Metric,null()), 

  if(GetFieldSelections(Sector, ',', 10000, '$')='Industry',If(match([_Metricsector],'All','Industry'),_Metric,null()), 

  if(GetFieldSelections(Sector, ',', 10000, '$')='Financial',If(match([_Metricsector],'All','Financial'),_Metric,null()),null())))),_Metric), '|'), 'KPI2')