Formula inside a variable (inside a field)

[mrieuse]

Hi,

My goal is to create a radar chart with like 10 KPIs with complex formula.

I want to see for one country what is the value of each one of the KPI.

At first, I used such a code in the radar measure:

=Peek(Match(KPI_id, 1, 2, 3, ...), $(vKPI1), $(vKPI2), $(vKPI3), ...)

And each one of the variable vKPIi contains the formula of the KPI.

But I would like to do something smarter.

The list of the KPIs is in a dimension, with these columns:

KPI_name | KPI_id | KPI_variable

My KPI 1  | 1 | KPI1

My KPI 2 | 2 | KPI2

My KPI 3 | 3 | KPI3

The list of KPIs could evolve frequently, I am not very pleased with the solution based on the ID.

I would like to directly use the value of the field KPI_variable to get the formula of the KPI.

I tried a lot of things but couldn't achieve to do it.

Please help needed 🙂

[MikeW · Accepted solution]

I think you mean to use pick(match()) instead of peek(match())? 

You can use something like this for the expression, assuming KPI_variable contains the names of variables. 

$(='Pick(Match([KPI_id],'&Concat([KPI_id],',',[KPI_id])&'), $'&'('&Concat([KPI_variable],'),$'&'(',[KPI_id])&'))')

So first you build the measure in string format. Then put it in $(=string) to use it as an expression.

[mrieuse]

Hi,

Perfect, that's exactly what I tried to do by myself.

Thank you very much

François