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.

https://exceleratorbi.com.au/using-variables-dax/

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

Regards

Announcements

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