getfieldselection alternate state

[ilanbaruch]

hi all

in my model i have a pivot with 2 alternate state calculations

i want to add to the table calculation condition to only calculate if one of the alternate state dims are selected 

tried it with the below condition :

getselectedcount(Year,[State1]) >0 or getselectedcount(Year,[State2]) >0

but didnt succeed ,  any ideas ?

advanced thanks

[jwjackso]

If you are trying to determine if only 1 item is selected from the dimension, you can use a combination of Concat  and SubStringCount .

Suppose that your Year dimension only has 2018, 2019, and 2020.  

If nothing was selected, using the following Concat({[State1]}Year,'|') would return 2018|2019|2020.  If 2019 was selected, the statement would return 2019.  If 2019 and 2020 were selected, the statement would return 2019|2020.  Combining the the Concat with SubstringCount, SubstringCount(Concat({[State1]}Year,'|'),'|') = 0 when only 1 year is selected.

Try:

SubstringCount(Concat({[State1]}Year,'|'),'|') = 0 or SubstringCount(Concat({[State2]}Year,'|'),'|') = 0

[ilanbaruch]

hi Jwjackso,

when adding : Concat({[State1]}Year,'|')

i receive duplication of the year selected in state 1 , so the SubstringCount retrieves a huge number..

[jwjackso · Accepted solution]

Add Distinct

Concat({[State1]} Distinct Year,'|')

[ilanbaruch]

yap. that did the trick.

thank you