How to filter on column value

I have a dataset like below. My quicksight dashboard has control on site column. How can I create a report to show the #1 site based on the rank of the group that site belongs to? for example, if I select site CCC, the report should display site AAA which is the #1 site in Group A (Site CCC belongs to group A)

Site Group Rank
AAA Group A 1
BBB Group A 2
CCC Group A 3
DDD Group B 1
EEE Group B 2
FFF Group C 1
GGG Group C 2
HHH Group C 3
III Group C 4

image

  • define a parameter called SiteParam and add parameter control to the analysis
  • add calculated field called SiteOrNull with expression
    ifelse(Site=${SiteParam}, Site, NULL)
  • add calculated field called minRank with expression
    minOver(ifelse(isNull(SiteOrNull), NULL, Rank), [Group], PRE_AGG)
  • add calculated field called minRank2 with expression
    minOver(ifelse(isNull(minRank), NULL, Rank), [Group], PRE_AGG)
  • add calculated field called toShow with expression
    ifelse(Rank=minRank2, ‘Show’, ‘Hide’)

at this point in time it should look similar to

Now add a filter on toShow column to remove rows with “Hide” value

3 Likes

Another solution with top/bottom filter:

  1. Create a parameter called “Site” and add it as control. (Site control get value from site column of dataset).
  2. Create a field called “isSite” with expression: “distinct_countIf(Site, Site=${Site})”. Now, the data looks like:
  3. Add a filter:
  4. Add another filter:
1 Like

Thank you so much. I will try this tomorrow and update here.

Thank you so much. I will try this one too and update here.