Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi,
Use an aggregate function.
This code:
MAxDATA = MAX(table[colum3])
The new table will look like this, is this what you need?
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
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
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
Hi,
I cannot understand your question. The two images in your question are exactly the same. What is the problem?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |