I have three columns: a column of dates, a column of numerical values, and a a column of strings (states for example). I would like to create a column that assigns a 1 to any row in which the numerical value column is the largest value for each date and each string, and a 0 for all else. Is there a way to do this? For example, say there is are 5 different rows for every date in a year and every state. One out of each of these 5 rows should have a value of 1, as this row has the maximum numerical value out of the 5.

I tried using SQL in my access database to create a calculated column but found no luck. I also attempted to use the FIXED function in tableau but since my data sits on top of an access database, that function wasn’t available to me.