I liked the solution, but frankly speaking didn't understand few technical things so thought of asking like when selecting 2 regions, the index functioning is returning 0, may be because multiple selection is giving null in REGION(QV uses 'only' when no aggregation provided?) in index function. But its not impacting the output, not sure why.
Also I thought 'if' in calculated dimension will show one of the true condition as dimension in chart('REGION' or REGION) but it is showing both as desired, not sure how it is happening technically. Is it because of disregarded dimension REGION in expression, not sure how?
P.S. I tried with concat(REGION) in index function, it resulted in 1 as index output when multiple regions are selected, but since without it also its working, just wanted to know technical stuff behind it. Thanks in advance!
The calculated dimension takes each value in the REGION dimension in turn and applies the calculation to it.
If for example If we have Andalucia & Madrid selected. The part of the calculation that is GetFieldSelections(REGION, '|') will contain the string 'Andalucia|Madrid'.
So for the dimensions Andalucia and Madrid, the function Index( GetFieldSelections(REGION, '|'), REGION) will return a positive value because each of these dimensions can be found in the string 'Andalucia|Madrid'. Therefore in these 2 cases, the IF statement is true and will return the true part, i.e. the value in the field REGION .
In the case of dimension Cataluna, this value is not in the string 'Andalucia|Madrid', therefore the Index function returns zero, and the IF statment is false so the false part is returned, i.e. the string value 'REGION'.
So in this example you end up with 2 dimensions of Andalucia and Madrid because they were selected, plus the derived dimension 'REGION' which contains the unselected dimensions (in this case just Cataluna).
The values for all the dimensions get reported in the table because we used the set analysis clause in the expression to ignore the REGION selection filtering.