I have air quality data from 5 different locations over the course of a month. I've calculated the max PM2.5 between all the locations, but I would also like to determine on which date this max occurred.
I tried creating a calculated field using CASE:
CASE
WHEN Max_PM2.5 = School1 THEN Date
WHEN Max_PM2.5 = School2 THEN Date
...
END
where School1 is all of that school's data and Date is all of the date data, but you cannot combine aggregated and non-aggregated fields. I've also tried determining the position of the Max_PM2.5 value to find the corresponding date, but Data Studio doesn't have that functionality either. Does anyone have any suggestions?
It looks like this just isn't possible with Google Data Studio. I just did all the calculations in Google Sheets and pulled from there.