I have created a calculated field (say OverallStatus) based on another calculated field (say Status).
Now I am able to apply Status values as filters to KPI or any other visual, but unable to apply OverallStatus values as filter…
Can someone please help me
Hi @Pavitra
Welcome to the QuickSight community!
Could you please share the calculated field formulas so I can better assist you?
My assumption is that you are trying to calculate the OverallStatus based on aggregated values, making it a summary field that cannot be used as a direct filter in visuals.
To resolve this, create the OverallStatus at the row level, avoid using aggregations like sum(), avg().
Hi @Xclipse Thanks for you reply.
Please find below calculated fields I’m using in QuickSight
Status = ifelse(
Reason= 10, 'Non-Compliant',
(isNotNull(ValidUntil) and datediff(DateRisk,ValidUntil) < 0) , 'Non-Compliant',
datediff(DateRisk, GracePeriodStartDate) < 0, 'At Risk',
datediff(DateRisk,GracePeriodStartDate) < 30, 'Approaching Risk Window',
isNotNull(ValidFrom) = TRUE and isNull(ValidUntil) = TRUE, 'Compliant',
(isNotNull(GracePeriodStartDate) = TRUE and datediff(DateRisk,GracePeriodStartDate ) > 29), 'Compliant',
(IsExempt = 1 and dateDiff(DateRisk, ValidUntil) > 0), 'Compliant',
'Non-Compliant - no Data'
)
My initial approach for ContextStatus was as follows, but I wasn’t able to use it for applying filters to KPIs:
ifelse(
// Approaching Risk Window
sumOver(sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AllSatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
sumOver(sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) = 0
and
sumOver(sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
// At Risk
sumOver(sum(ifelse(Status = 'At Risk' and Type = 'AllSatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'At Risk',
sumOver(sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) <
sumOver(sum(ifelse(Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName])
and
sumOver(sum(ifelse(Status = 'At Risk' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'At Risk',
//Non-Compliant
sumOver(sum(ifelse(Status = 'Non-Compliant' and Type = 'AllSatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
sumOver(sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) = 0
and
sumOver(sum(ifelse(Status = 'Non-Compliant' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
//Compliant
sumOver(sum(ifelse(Status = 'Compliant' and Type = 'AllSatisfied', 1, 0)), [RegistrantId, ContextName]) =
sumOver(sum(ifelse(Type = 'AllSatisfied', 1, 0)), [RegistrantId, ContextName]),
'Compliant',
sumOver(sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0)), [RegistrantId, ContextName]) > 0,
'Compliant',
// Fallback
'Non-Compliant'
)
However, I was able to update the ContextStatus formula, and it’s now working as expected—especially for filters and KPIs:
check_ContextStatus = ifelse(
// Approaching Risk Window
sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) = 0
and
sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
// At Risk
sum(ifelse(Status = 'At Risk' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'At Risk',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) <
sum(ifelse(Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName])
and
sum(ifelse(Status = 'At Risk' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'At Risk',
//Non-Compliant
sum(ifelse(Status = 'Non-Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) = 0
and
sum(ifelse(Status = 'Non-Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
//Compliant
sum(ifelse(Status = 'Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) =
sum(ifelse(Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]),
'Compliant',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Compliant',
// Fallback
'Non-Compliant'
)
Now, I want to update my OverallStatus based on this logic, so I can use it for filters and KPIs. Here’s the formula I’m working with:
OverallStatus = ifelse(
sumOver(ifelse(ContextStatus = 'Non-Compliant', 1, 0), [RegistrantId]) > 0, 'Non-Compliant',
sumOver(ifelse(ContextStatus = 'At Risk', 1, 0), [RegistrantId]) > 0, 'At Risk',
sumOver(ifelse(ContextStatus = 'Approaching Risk Window', 1, 0), [RegistrantId]) > 0, 'Approaching Risk Window',
sumOver(ifelse(ContextStatus = 'Compliant', 1, 0), [RegistrantId]) > 0, 'Compliant',
'Non-Compliant'
)
Your help is greatly appreciated 
Thanks in advnce
Hi @Pavitra
Please try the following formula for calculating OverallStatus in your analysis. I’ve tested this across sample data and it appears to work as expected.
Example:
OverallStatus = ifelse(
sumIf(1, {Check_ContextStatus} = 'Non-Compliant') > 0, 'Non-Compliant',
sumIf(1, {Check_ContextStatus} = 'At Risk') > 0, 'At Risk',
sumIf(1, {Check_ContextStatus} = 'Approaching Risk Window') > 0, 'Approaching Risk Window',
sumIf(1, {Check_ContextStatus} = 'Compliant') > 0, 'Compliant',
'Non-Compliant'
)
hi @Xclipse The formula you provided for OverallStatus doesn’t appear to be appropriate, as it doesn’t group by RegistrantId. As a result, it simply reflecting the value of ContextStatus
Please refer to the screenshot below — check_OverallStatus is the formula you shared.
Additionally, I’m unable to apply a filter to the visual.
When I use a custom filter, I can only select “Non-Compliant,” which retrieves the distinct count of RegistrantId. This indicates that the filter value isn’t being parsed correctly. For other statuses, the visual displays “No Data.”
Hi @Pavitra
Create a parameter with the desired filter values and apply it to your filter. This will dynamically apply the selected value from the control to your visual.
Additionally, you can add RegistrantId to a table and then hide the column. This will still group the data by RegistrantId behind the scenes. Refer the following images.
Hi @Xclipse
Thanks for your input!
I was able to apply the filter to the table. However, my main concern is displaying the total number of registrants in the KPI for the different values of OverallStatus, i.e., making the KPI visual filterable.
Here’s the formula I’m currently using for OverallStatus:
OverallStatus =
ifelse(
sumOver(ifelse(ContextStatus = 'Non-Compliant', 1, 0), [RegistrantId]) > 0, 'Non-Compliant',
sumOver(ifelse(ContextStatus = 'At Risk', 1, 0), [RegistrantId]) > 0, 'At Risk',
sumOver(ifelse(ContextStatus = 'Approaching Risk Window', 1, 0), [RegistrantId]) > 0, 'Approaching Risk Window',
sumOver(ifelse(ContextStatus = 'Compliant', 1, 0), [RegistrantId]) > 0, 'Compliant',
'Non-Compliant'
)
Initially, ContextStatus was also not filterable in the KPI, but I was able to modify it to check_ContextStatus, which is now filterable.
I would appreciate your help in constructing the OverallStatus formula so that it can be filterable in the KPI, or any other way to parse the data effectively.
Hi @Pavitra
Could you please try the following calculated fields. I’ve tested this with sample data and it appears to work as expected.
Example:
ContextStatus =
ifelse(
// Approaching Risk Window
sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) = 0 and
sum(ifelse(Status = 'Approaching Risk Window' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Approaching Risk Window',
// At Risk
sum(ifelse(Status = 'At Risk' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'At Risk',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) <
sum(ifelse(Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) and
sum(ifelse(Status = 'At Risk' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'At Risk',
//Non-Compliant
sum(ifelse(Status = 'Non-Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) = 0 and
sum(ifelse(Status = 'Non-Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Non-Compliant',
//Compliant
sum(ifelse(Status = 'Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]) =
sum(ifelse(Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName]),
'Compliant',
sum(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName]) > 0,
'Compliant',
//Fallback
'Non-Compliant'
)
OverallStatus_New =
ifelse(
sumOver(sumIf(1, ContextStatus = 'Non-Compliant'), [RegistrantId]) > 0, 'Non-Compliant',
sumOver(sumIf(1, ContextStatus = 'At Risk'), [RegistrantId]) > 0, 'At Risk',
sumOver(sumIf(1, ContextStatus = 'Approaching Risk Window'), [RegistrantId]) > 0, 'Approaching Risk Window',
sumOver(sumIf(1, ContextStatus = 'Compliant'), [RegistrantId]) > 0, 'Compliant',
'Non-Compliant'
)
@Xclipse The Overall Status is working but I’m unable to filter the values in KPI. I tried to pass the parameter in filter – no luck
Hi @Pavitra
KPI visual can’t be used with dimension fields. It is specifically designed to display key performance metrics using measures (numerical fields). To use a KPI, you must use a measure for both the value and the optional target.
I tried applying a filter using parameter values as shared in the previous responses. It seems to be working fine in the table visual type.
Hi @Xclipse I understand that we use KPIs to measure performance. However, in this case, we are using a KPI visual, similar to a Card visual in Power BI. We are setting the value as the DistinctCount of RegistrantId and leaving the Target value blank.
Additionally, we are applying a filter on top of it.
I’m able to use check_ContextStatus values to filter the KPI visual, but the problem is with OverallStatus 
Hi @Pavitra
Sorry, I’m out of ideas. I’m unable to understand the issue.
I’m assuming the issue occurs when applying a filter using the OverallStatus calculated field.
The issue could be due to the calculated field referencing multiple other fields. In such cases, you need to include the group of fields used in the calculation in the table and then apply the filter.
Hi @Xclipse
I came across the post : New to QS - Aggregating inside Calculated Fields for KPI - Question & Answer - Amazon QuickSight Community and have updated my calculated fields based on David’s advice, as shown below:
new_ContextStatus = ifelse(
// Approaching Risk Window
sumOver(ifelse(Status = 'Approaching Risk Window' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Approaching Risk Window',
sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) = 0
AND
sumOver(ifelse(Status = 'Approaching Risk Window' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Approaching Risk Window',
// At Risk
sumOver(ifelse(Status = 'At Risk' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'At Risk',
sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) <
sumOver(ifelse(Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG)
and
sumOver(ifelse(Status = 'At Risk' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'At Risk',
//Non-Compliant
sumOver(ifelse(Status = 'Non-Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Non-Compliant',
sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) = 0
and
sumOver(ifelse(Status = 'Non-Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Non-Compliant',
//Compliant
sumOver(ifelse(Status = 'Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) =
sumOver(ifelse(Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG), 'Compliant',
sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Compliant',
'Non-Compliant'
)
and
new_OverallStatus = ifelse(
sum(ifelse({new_ContextStatus} = 'Non-Compliant', 1, 0), [RegistrantId]) > 0, 'Non-Compliant',
sum(ifelse({new_ContextStatus} = 'At Risk', 1, 0), [RegistrantId]) > 0, 'At Risk',
sum(ifelse({new_ContextStatus} = 'Approaching Risk Window', 1, 0), [RegistrantId]) > 0, 'Approaching Risk Window',
sum(ifelse({new_ContextStatus} = 'Compliant', 1, 0), [RegistrantId]) > 0, 'Compliant',
'Non-Compliant'
)
I’m now able to apply filters in the KPI
However, I’m facing one remaining issue: I’m unable to use the new_OverallStatus field in charts like Pie, Donut, Bar, etc.
I’m receiving the following error:
The Level Aware Calculation (LAC) aggregate expression doesn’t work with other category calculation at this moment. Please remove the LAC aggreagtion expression from metrics or visual dimensions, or turn off the default other category calculation in visual options
Would you have any suggestions on how to resolve this?
Tagging @David_Wong huge thanks again for your insights!
1 Like
