How to filter on column value

[pmmadhur]

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

[Tatyana_Yakushev]

• 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

[Ying_Wang]

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:
   
   

[pmmadhur]

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

[pmmadhur]

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

[tao]

sharing yet another super SIMPLE solution:

Add a calculated field - let’s call it Top Site. Here’s the formula for it: firstValue(Site, [Rank ASC], [Group])

should look like this: