Solved: Expressions - Page 2

Jonathan_S · 01-24-2020 07:08 AM

I have a question with the max row fuction.

I have the Following Formula which is working but it is returning the ID of the row, I need it to return the Date Value of the Row, Am I doing somthing wrong?

Thanks,

If(IsNotBlank(MAXROW(“Daily Production Record”, “Date”,And( [Customer] = [_THISROW]. 
[Customer],[Flavor] = [_ThisRow].[Flavor], [Con/Org/GF] = [_ThisRow].[Con/Org/GF],[Waffles per 
 Display] = [_ThisRow].[Waffles per Display], [Waffles per Master] = [_ThisRow].[Waffles per 
 Master]))),CONCATENATE("Last Produced on: " , MAXROW(“Daily Production Record”, “Date”,And( 
 [Customer] = [_THISROW].[Customer],[Flavor] = [_ThisRow].[Flavor], [Con/Org/GF] = [_ThisRow]. 
[Con/Org/GF],[Waffles per Display] = [_ThisRow].[Waffles per Display], [Waffles per Master] = 
 [_ThisRow].[Waffles per Master]))), " " )

Steve

You would need to add another virtual column to your Types table.

Alternatively, here’s a single expression that does everything:

ANY(
  LIST(
    CONCATENATE(
      "Last Produced on: ",
      LOOKUP(
        MAXROW(
          “Daily Production Record”,
          “Date”,
          AND(
            ([Customer] = [_THISROW].[Customer]),
            ([Flavor] = [_THISROW].[Flavor]),
            ([Con/Org/GF] = [_THISROW].[Con/Org/GF]),
            ([Waffles per Display] = [_THISROW].[Waffles per Display]),
            ([Waffles per Master] = [_THISROW].[Waffles per Master])
          )
        ),
        “Daily Production Record”,
        “row-key”,
        “Date”
      )
    )
  )
  - LIST("Last Produced on: ")
)

MAXROW(...) gets the key column value of the row matching your selection criteria and with the most recent date.
LOOKUP(...) uses the key column value from (1) to get the Date column value from the row it identifies. Replace row-key with the name of the key column of the Daily Production Record table.
CONCATENATE(...) constructs the desired text from the date gathered in (2).
(LIST(...) - LIST("Last Produced on: ")) constructs a one-item list from the result of (3), then removes from that list any items that have the exact value, Last Produced on: . If MAXROW() didn’t find anything, then LOOKUP() won’t find anything, and CONCATENATE() won’t have anything to add to Last Produced on: , so CONCATENATE()'s output will be only that. List subtraction allows us to remove that undesirable result.
ANY(...) gives the first (and only) item from the list resulting from (4). Note that if (4) produced an empty list by removing an undesirable result, ANY() will produce a blank value.

View solution in original post