FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()

Steve
Participant V
102 REPLIES 102

Steve
Participant V

How do I get a column value from the last row of the spreadsheet?

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

See also: MAX()

Thanks for this!

thx for this steve!

thanks

it works!

pero esta formula solo me trae el valor maximo, no el ultimo valor

ยฟEn quรฉ sentido? ยฟUn valor de la fila del mayor [_ROWNUMBER] no es de la รบltima fila?

Si, el ultimo valor de la columna que necesites encontrar

Te recomiendo hacer tu pregunta en una conversaciรณn nueva, especificando los detalles particulares de lo que pretendes hacer. Asรญ, es probable que otros miembros de la comunidad vean tu pregunta y puedan aportar ayuda.

perdona, no preguntaba, respondรญa, saludos.

quiero decir si tienes una columna de precios por ejemplo 10,100,50,20 el valor que te trae es 100 por ser el mรกximo valor no 20 por ser el ultimo, lo digo por que se utiliza Max, y por que cuando probรฉ la formula eso sucediรณ, no se si estoy equivocado, gracias por tu respuesta

El resultado de la expresiรณn siguiente debe ser 20, no 100.

[_ROWNUMBER][Precio]
110
2100
350
420
LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Precio"
)

 

SI, el MAX de una columna te trae el valor maximo de esa columna.

Pero en el ejemplo del inicio usa LOOKUP junto con MAX

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

 

Te trae el ultimo valor de la columna que necesites.

pada tabel form type kolom text, max tidak berhasil, mohon bantuannya?

Steve
Participant V

How do I get the last row of the spreadsheet?

MAXROW(
  "My Table",
  "_ROWNUMBER"
)

Replace My Table with the name of the table whose row you want.

How do I get a column value from the last row of this thing only?

LOOKUP(
  MAX(
    SELECT(
      My Table[_ROWNUMBER],
      ([_THISROW].[Thing] = [Thing])
    )
  ),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want.

See also: MAX()

Thanks a lot for this @Steve

Hi can I use this to:
get the value in row 1 col 1 and use that as the initial value of row 2 col 1?

Use case: I have 1 form where sometimes only 1 column is updated and the other columns of the form need to be filled with the previous rowโ€™s values for those columns.

Yep.

Thank you!

Hi @Steve,

I have an issue and I have not been able to solve it, I hope you can help me โ€ฆ

I need to sort a related column by date in descending order.

Thanks

Thanks a lot for that @Steve
Was wondering how to set the location for a stocktaking app dependable from the user, worked like a charm!
Thanks again

Steve
Participant V

How do I get a list of rows of this thing only?

FILTER(
  "My Table",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

Hope you can help!

Steve
Participant V

How do I get a list of column values from rows of this thing only?

To include duplicate values in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; Wanted Column with the name of the column whose value you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

To omit duplicates so that each value occurs only once in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing]),
  TRUE
)

Where should this expression be entered to have a unique list of rows?

Hi Steve,

I have a table with multiple columns. I wanted to have a unique list of rows for the following:

  • list of unique values from [service towers] under [segment].
  • list of unique values from [list transactions] under [service towers] under [ segment].

The list I hope to use in an interactive dashboard. Values will change after selecting [service towers] and [list transactions].

This is my first app and would appreciate all the guidance you can give.

Hello,

I have tried to organize this list in alphabetical order and I have not succeeded, so I go to you to guide me to achieve it.

substitute(
			SELECT(
					registro alumnos[Nombre],
			
      		or(
          	([Categoria]=[IdPartidoLista].[_THISROW].[Categoria]),
            ([Categoria]=number([IdPartidoLista].[_THISROW].[Categoria])+1),
            ([Categoria]=number([IdPartidoLista].[_THISROW].[Categoria])-1)
          )
      
			),
	",",
	"
	"&
)

For your support, thank you.

what I have not been able to find is the correct syntax, can you guide me with this?

Thank you

SORT() accepts a List as an argument. SELECT() returns a List. Therefore:

SORT( SELECT(...) )

@Marc_Dillon,
Thank you very much, it worked well for me

Thank you

Steve
Participant V

How do I do SUMIF() or SUMIFS()?

SUM(
  SELECT(
    Orders[Total Amount],
    AND(
      ([Customer] = [_THISROW].[Customer]),
      ([Order Date] >= [_THISROW].[Order Date])
    )
  )
)

Use SELECT() to gather values from a single column only.

See also: AND(), SUM()

Puedes por favor explicar la sintaxis de esta formula con un ejemplo ?

  1. SUM(...) sums the list of values (...) produced by (2).

  2. SELECT(Orders[Total Amount], ...) gathers a list of values from the Total Amount column of select rows (...) in the Orders table.

  3. AND(...) selects only those rows in the Orders table that match all of the given criteria (...; see (3) & (4)).

  4. ([Customer] = [_THISROW].[Customer]) selects only rows with a Customer column value that matches the Customer column value of the row receiving the SUM() result in (1). Rows that have the same customer.

  5. ([Order Date] >= [_THISROW].[Order Date]) selects only those rows in the Orders table with an Order Date column value greater than or equal to the Order Date column value of the row receiving the SUM() result in (1). Orders on or after the given date.

Steve
Participant V

How do I do COUNTIF() or COUNTIFS()?

COUNTIF(col, expr), where col is a whole-column reference (e.g., A), is similar to:

COUNT(
  FILTER(
    "table",
    ([col] expr)
  )
)

COUNTIFS(col1, expr1, col2, expr2, ...), where col1 and col2 are each whole-column references, is similar to:

COUNT(
  FILTER(
    "table",
    AND(
      ([col1] expr1),
      ([col2] expr2),
      ...
    )
  )
)

To limit the rows included in the COUNT() range (e.g., as with COUNTIF(A3:A7, ...)), include the limit criteria in the FILTER() sub-expression, similar to this:

COUNT(
  FILTER(
    "table",
    AND(
      ([_ROWNUMBER] >= low-row-num),
      ([_ROWNUMBER] <= hi-row-num),
      ([col] expr)
    )
  )
)

To count across multiple columns (e.g., as with COUNTIF(A:C, ...)), you may add the results of an additional FILTER() sub-expression for each additional column:

COUNT(
  FILTER(...)
  + FILTER(...),
  ...
)

See also: AND(), COUNT()

Hi Steve!

I follow your solutions a lot!

How do I do something similar with MAXIFS

MAX(
  SELECT(
    table[column],
    match-expr
  )
)
Top Labels in this Space