Prevent nulls in drilldown

I have a set of data which are ordered by levels of unequal hierarchies, that means that some of them have two levels, others have 6 levels and they are also branched.

it happens that when i want to show this data in a pie chart, ah to go down a level in the grouping of the data, the data nulls are shown. i want them not to be shown. i can’t apply a filter as the whole row is filtered, making the data in higher levels disappear.

i found this but it is not really useful Creating cascading filters - Amazon QuickSight

here is an example of the data set and chart with null

Is my data model not suitable for this task, or is there a solution that I don’t see?


so what you want is:
you switch to the lowest level and the data of the sub_sub_sub_level will be the “new” 100% and then you display GERENCIA ESTAC with 87% and GERENCIA OPERACIONES with 13%
is this correct?

I think you will not get a good solution with the drill down feature and your data.

You could solve this with a dropdown, where your users can choose the level which is display in the visual and use a calculated field as a value, which has a ifelse function that checks, if the dropdown has a specific value and for this value you use filtered data.

ifelse(PARAMETER <> ‘sub_sub_sub_level’,value,
ifelse(PARAMETER = ‘sub_sub_sub_level’ AND IsNotNull(sub_sub_sub_level),value,0))


1 Like