I created a boolean variable by using the function below so that the variable DIV ADSCR < 1 SCN 1 IND has value of 0 or 1 .
I wonder how I can drop those rows with DIV ADSCR < 1 SCN 1 IND =0 and just keep the rows DIV ADSCR < 1 SCN 1 IND =1. Building a filter on the dashboard is not what I want. I want to use a calculated field to do so.
Similar to what you did, you can set up a Boolean calculated field returning 1 if the condition you want is met and 0 otherwise. Then set up a filter on this new field with value equals 1 so that all rows that do not match the condition in your calculated field are dropped.
It’s a bit difficult to debug the problem like this but in general a good rule of thumb is to keep aggregate functions only in the outermost calculated field - in order to avoid having nested aggregate function which are not allowed in QuickSight. Of course, it is very common to need to sum and count things at multiple levels… but in the underlying fields you could use sumOver, countOver and other LAC-w functions with PRE_AGG or PRE_FILTER calculation levels (since these operate on a row by row level and not on groups/aggregates of data). This way you would be “aggregating” data without using the so called aggregate functions - so you would be able to use them at the last level.