cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculate text from row with top value from a filtered table

Hello,

I have this "Table1".


Column1Column2Column3

AX01/01/2017
BY07/03/2017
CZ22/04/2017
AX09/07/2017
BW30/10/2017
CU16/11/2017
AY18/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
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. Smiley Happy

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

c1.PNG

 

 

Regards

View solution in original post

5 REPLIES 5
Highlighted
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Microsoft
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. Smiley Happy

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

c1.PNG

 

 

Regards

View solution in original post

Highlighted
Resolver IV
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?

Clipboard01.gif

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
Microsoft

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

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. Smiley Happy

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

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

 

Regards

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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