How to return the value of a column in the last row?

[djmalaka]

Hi all,
I have created a slice out of a table called “Kidney”. In this “Kidney” table, there is a column called [Station], where user has filled in already with different hospital names in the country. What I want is to write a filter expression for the slice to just filter out the rows in the “Kidney” table to only display a set of rows that has the [Station] name decided by the user in a different table called “Analyser”. In the “Analyser” table also there is a column called [Station], and when the user selects a ‘Station’ name and saves it, I have written a behaviour expression to bring out a “Chart” that picks data from the “Kidney” table’s slice. So what I want is this filter expression inside the slice to have that particular ‘Station’ name for the [Station] column so that the Chart would only show relevant data for that ‘Station’.
I tried a few expressions including a LOOKUP, but didn’t work for me.
Since there’s no row level undersanding between these 2 tables, I guess the best way to bring up the filter expression is to call out the last filled row in the “Analyser” table. But how to write an expression for that? Would appreciate your help!

[Steve]

Try as the row filter expression for the slice:

IN(
  [Station],
  SELECT(
    Analyser[Station],
    ([_ROWNUMBER] = MAX(Analyser[_ROWNUMBER]))
  )
)

1. IN([Station], ...) matches only those rows in the Kidney table (the slice’s source table) with a Station column value that matches any of the given list of values (...; see (2)).

2. SELECT(Analyser[Station], ...) gathers from the Analyser table a list of Station column values from rows that match the given criteria (...; see (3)).

3. ([_ROWNUMBER] = MAX(Analyser[_ROWNUMBER])) matches only those rows where the row’s _ROWNUMBER column value is equal to the largest row number in the Analyser table.

(3) finds the last row of the Analyser table, (2) gets the Station value from that row, (1) compares it to the Kidney row’s Station column value.

[djmalaka · Accepted solution]

Thank you very much Steve! Exactly what I was looking for and it worked like a charm! Thank you for the explanation as well.

[Stan_Malone]

Steve:

IN( [Station], SELECT( Analyser[Station], ([_ROWNUMBER] = MAX(Analyser[_ROWNUMBER])) ) )

Spent two days trying to figure this solution, thank you!

[Kevin_Vandeness]

Hello Steve,

I’d like to know if this function works for decimal type columns. For example, in my case, I want to return the value of a column after saving it for the first time. This column is called [debit pompage] I’ve tried this formula :
DECIMAL(
IN(
[Debit pompage],
SELECT(
niveaux[Debit pompage],
([_ROWNUMBER] = MAX(niveaux[_ROWNUMBER]))
)
)
)

The app says that the function is correct, but I don’t get the right result I expect.

Thank you in advance.

[Steve]

Your expression doesn’t make sense to me, so I suspect it’s not doing what you want.

1. SELECT(niveaux[Debit pompage], ([_ROWNUMBER] = MAX(niveaux[_ROWNUMBER]))) finds the value of the Debit pompage column in the last row of the niveaux table.

2. IN([Debit pompage], ...) asks, “is the value of the Debit pompage column of this row equal t any of the values produced by (1)?” The answer is either TRUE (yes) or FALSE (no).

3. DECIMAL(...) converts the result of (2) to a Decimal value. I’ve never tried converting TRUE or FALSE to a Decimal value, but I would expect it to be either 0.0 or blank, neither of which I suspect you want.

What result are you looking for?

[ChrisLW]

This worked for me, and the column I needed to filter has monetary units (decimals).

[Kevin_Vandeness]

I have 3 tables :

• generalites
• suivi de pompage
• niveaux

In the table niveaux, I have a column [Debit pompage] and type is Decimal. When I will start my test, I will write down different informations as turbidity,dynamic level and Debit pompage. Let’s denote that all of these informations are decimal numbers. After the first test, I will save all of this informations and I will take these informations up again and save once again. I just want the decimal value of [Debit pompage] return in the last row after saving.

Just to let you know, my niveaux table has a link with suivi de pompage table thank to an unique ID and suivi de pompage table has a link with generalites table.

English is not my first language so, hope you will understand my need.

[Steve]

It sounds straight-forward. What I understand is that you will be taking at least one set of metrics and adding a row to the niveaux for each set. At the completion of your metrics-gathering, you want the last value gathered of the Debit pompage metric.

Your original expression was this:

DECIMAL(
  IN(
    [Debit pompage],
    SELECT(
      niveaux[Debit pompage],
      ([_ROWNUMBER] = MAX(niveaux[_ROWNUMBER]))
    )
  )
)

What I believe you want is:

ANY(
  SELECT(
    niveaux[Debit pompage],
    ([_ROWNUMBER] = MAX(niveaux[_ROWNUMBER]))
  )
)

or:

LOOKUP(
  MAX(niveaux[_ROWNUMBER]),
  "niveaux",
  "_ROWNUMBER",
  "Debit pompage"
)

Both are equivalent.

See also:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

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()

[Kevin_Vandeness]

It’s seem to be good. Thank you very much for your precious help @Steve !

[Joaquin_Otero1]

Hi @Steve, I’m blocked trying to filter the last row of each year on a slice.
Do you have any idea of how to do this?

Thanks.

[Lynn]

Hi @Joaquin_Otero1
Could you please start a new thread with your question and explain what you are trying to do and what your problem is thanks.