Set column's initial value to column's most common existing value

[dbaum]

What's the best way to set a column's initial value to the column's most common existing value? I experimented with various approaches and ultimately implemented the following, but feel like there must be a simpler technique.

In this example, I set the initial value for the Location column in the Events table to be the Location that is already the most common location across events.

Create a "Location Count" virtual column in the Events table to count the frequency of each event's location:

COUNT(
	SELECT(Events[Location Code], 
  [Location Code] = [_THISROW].[Location Code])
  )

For the Location column's initial value, combine the LOOKUP and MAXROW functions to find the location with the highest count:

LOOKUP(
	MAXROW(Events, "Location Count"), 
  "Events", 
  "Event ID", 
  "Location Code"
  )

[1minManager]

ANY(Select(Events[Location Code],[Location Count]=

MAX(Select(Events[Location Count],TRUE))

))

So the MAX(Select... finds he highest count then the ANY(Select... finds the first [Location Code] which has this number.  Not very elegant but should work.

Simon@1minManager.com

[dbaum]

Thanks. That seems pretty equivalent to my LOOKUP expression.

Let's see whether other experts come up with something even better--ideally, obviating the need to maintain the virtual column in the first place.

Meanwhile, suggested a new MODE function.

[Steve · Accepted solution]

Untested, but you could experiment with this:

ORDERBY(
  keys-list,
  COUNT(FILTER(table, ([_THISROW-1].[column] = [column]))),
    TRUE
)

Note that using a virtual column for this is going to get really expensive really fast. Consider wrapping the App formula expression for the virtual column with:

IFS(
  ("Form" = CONTEXT("ViewType")),
    ...
)

[dbaum]

Hooray! Expression superhero @Steve does it again. Thanks a lot.

Somewhere along the way, I had tried a similar technique using [_THISROW], but hadn't tried including the "-1". I had assumed that [_THISROW] and [_THISROW-1] were equivalent in this context since there were only two levels of reference involved. If you have time at some point, it would be helpful to add here a conceptual explanation of your expression.

For those helped by referencing a concrete example, here's an updated version using the Locations and Events scenario from my original post. See that I wrapped Steve's expression in an INDEX() function to yield a single value for the column's Initial value property rather than the list value that is the output of the ORDERBY() function.

INDEX(
  ORDERBY(
    Locations[Location Code],
    COUNT(
    	FILTER(
      	Events, 
        [Location Code] = [_THISROW-1].[Location Code], 
        )
      ), 
    TRUE
  	), 
  1)

I've deleted my virtual column since it's no longer needed. Your idea to add a CONTEXT() condition to such columns is also helpful technique to keep in mind.

[Steve]

---

@dbaum wrote:

Somewhere along the way, I had tried a similar technique using [_THISROW], but hadn't tried including the "-1". I had assumed that [_THISROW] and [_THISROW-1] were equivalent in this context since there were only two levels of reference involved. If you have time at some point, it would be helpful to add here a conceptual explanation of your expression.

---

My expectation (again, having not tested it myself) is that [_THISROW-1] in this context refers to the row ORDERBY() is currently considering.