Dynamic Measures for LAC Expressions using Parameters

Question & Answer
, , ,
1

The users of my dashboard would like the flexibility to choose which metrics appear in visuals, so I was following this tutorial: https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Interactivity-Dynamic-Dimensions-and-Measures

However, when I create the calculated field for the measure parameter using ifelse, I see the following error: “The calculation operated on Level Aware Calculation (LAC) agg expressions is not valid.” Is there a workaround to this?

1 Like
2

Hi @afryc

Can you share the calculated field expression you are using?

Thanks,
Raj

3

Hi @rajjaya

This is the calculated field:

ifelse(
${pMeasure1} = 'Total Cost', {_total_cost},
${pMeasure1} = 'Impressions', {_impressions},
${pMeasure1} = 'Custom Conversions', {_custom_conversions},
${pMeasure1} = 'CPA', {_custom_cpa},
{_total_cost}
)

{_total_cost} and {_impressions} are both calculated fields with the MAX() calculation partitioned over the same fields, {_custom_conversions} is a MAX() calculation partitioned over a different set of fields, and {_custom_cpa} = sum({_total_cost})/sum({_custom_conversions})

4

Hi @afryc,

I’ve produced the same error you have here:

image
image778×166 6.73 KB

The limitation appears to be when including calculated fields in the DynamicMeasure calculated field that are a mixture of both aggregate and LAC functions.

In my example above, MaxProfitLAC is a LAC max() function, while MaxSales is an normal aggregate max() function.
Only if both calculated fields use the same type of function, both aggregate max(), or both LAC max(), then the calculated field is valid (noting that the LAC function grouping keys must be the same).

Were you able to find a workaround?

Many Thanks,
Andrew