cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate text from row with top value from a filtered table

Hello,

I have this "Table1".

Column1Column2Column3

 A X 01/01/2017 B Y 07/03/2017 C Z 22/04/2017 A X 09/07/2017 B W 30/10/2017 C U 16/11/2017 A Y 18/12/2017

I want a measure/column that from the rows with same Column1 value as the current row, gives the Column2 value of the row with Column3 highest value. I have got this with this formula and a matrix visual, but i have not got it with just a formula.

Measure = CALCULATE ( LASTNONBLANK ( Table1[Column2]; MAX ( Table1[Column3] ) ) )

This is the kind of thing i have tried:

Measure =
CALCULATE (
LASTNONBLANK ( Table1[Column2]; MAX ( Table1[Column3] ) );
CALCULATETABLE ( table1; FILTER ( table1; Table1[Column1] = Table1[Column1] ) )
)

Thanks!
Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Calculate text from row with top value from a filtered table

Hi @Nachospt,

I want a measure/column that from the rows with same Column1 value as the current row, gives the Column2 value of the row with Column3 highest value. I have got this with this formula and a matrix visual, but i have not got it with just a formula.

If I understand you correctly, you should be able to use the formula below to create new calculate column in your table to get the expected result in your scenario. ```Column =
VAR maxColumn3 =
CALCULATE ( MAX ( Table1[Column3] ), ALLEXCEPT ( Table1, Table1[Column1] ) )
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[Column2], 1 ),
FILTER (
ALL ( Table1 ),
Table1[Column1] = EARLIER ( Table1[Column1] )
&& Table1[Column3] = maxColumn3
)
)
``` Regards

5 REPLIES 5
Highlighted Super User III

## Re: Calculate text from row with top value from a filtered table

Hi,

I cannot understand your question.  The two images in your question are exactly the same.  What is the problem?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted Microsoft

## Re: Calculate text from row with top value from a filtered table

Hi @Nachospt,

I want a measure/column that from the rows with same Column1 value as the current row, gives the Column2 value of the row with Column3 highest value. I have got this with this formula and a matrix visual, but i have not got it with just a formula.

If I understand you correctly, you should be able to use the formula below to create new calculate column in your table to get the expected result in your scenario. ```Column =
VAR maxColumn3 =
CALCULATE ( MAX ( Table1[Column3] ), ALLEXCEPT ( Table1, Table1[Column1] ) )
RETURN
CALCULATE (
FIRSTNONBLANK ( Table1[Column2], 1 ),
FILTER (
ALL ( Table1 ),
Table1[Column1] = EARLIER ( Table1[Column1] )
&& Table1[Column3] = maxColumn3
)
)
``` Regards

Highlighted Resolver IV

## Re: Calculate text from row with top value from a filtered table

Hi,

Use an aggregate function.

This code:

`MAxDATA = MAX(table[colum3])`

The new table will look like this, is this what you need? Highlighted
Frequent Visitor

## Re: Calculate text from row with top value from a filtered table

Yes, that works perfectly 🙂
Thanks!

Do you know any resource or page to learn when to use an inline VAR like you did?

Regards!
Nacho

Highlighted Microsoft

## Re: Calculate text from row with top value from a filtered table

Based on my experience, the key to use the VAR function is to understand when and how a variable expression is calculated.

Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

In addition, followings are some good articles about using variables in DAX for your reference. https://exceleratorbi.com.au/using-variables-dax/

https://www.sqlbi.com/articles/defining-variables-in-dax-queries/

Regards

Announcements #### Get Ready for Power BI Dev Camp #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más! Top Solution Authors
Top Kudoed Authors
Users online (1,207)