Calculate text from row with top value from a filtered table

[Nachospt]

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

[Rfranca]

Hi, 

Use an aggregate function.

This code:

MAxDATA = MAX(table[colum3])

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

[v-ljerr-msft · Accepted solution]

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

[Nachospt]

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

[v-ljerr-msft]

Hi @Nachospt

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

[Ashish_Mathur]

Hi,

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