Max value from Column B for each distinct value from Column A

[dbaum]

Does anyone have a technique for accomplishing anything even approximating the type of function available in other platforms that is often known as "for each"? I need to determine the max value of one column that is associated with each distinct value in another column.

I think the following is the expression to evaluate whether the [Last Access] date for a given email was after the email was last added to any [Tenant ID] and before it was last removed (i.e., the log's [Added] column is false) from any [Tenant ID]. What I really need to evaluate is whether an email's [Last Access] date falls between its added and removed dates for each [Tenant ID] to which it has ever been added.

All that occurs to me is to SELECT the list of [Tenant ID] values associated with the email and evaluate for each using the INDEX function. In this case, I'd have to decide in advance the likely maximum number of [Tenant ID] values that any one email will be associated to so that I can explicitly make that many passes with INDEX--i.e., for the value in position 1, then position 2, then position 3, ....

Better approaches are obviously welcome.

AND(
  IF(
  	ISBLANK(
    	SELECT(
      	User Account Log[Log ID], 
        [Email] = [_THISROW].[User Email]
        )
      ), 
    false, 
	  [Last Access] >= 
    MAX(
    	SELECT(
      	User Account Log[Created], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          [Added]
          )
        )
      )
    ), 
  IF(
  	ISBLANK(
    	SELECT(
      	User Account Log[Log ID], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          NOT([Added])
          )
        )
      ), 
    true, 
    [Last Access] < 
    MAX(
    	SELECT(
      	User Account Log[Created], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          NOT([Added])
          )
        )
      )
    )
  )