Forum Discussion
Max value with date
Hi,
I need to get the max value in a category with its corresponding date (preferably in DAX).
the example as follow
| Table1 | ||
| City | Value | Date |
| RIY | 10 | 10/9/2019 |
| RIY | 25 | 10/10/2019 |
| RIY | 11 | 10/11/2019 |
| MKK | 8 | 10/10/2019 |
| MKK | 9 | 10/9/2019 |
The result should be like this
| Result | ||
| City | Value | Date |
| RIY | 25 | 10/10/2019 |
| MKK | 9 | 10/9/2019 |
Thank You in advance for your help
Thought it would be worth mentioning that the first answer will still have all the original values with an extra column identifying the highest for a product type without further work. I'm adding this in case a new table is wanted that only has those highest values and not the whole original table.
First, add a calculated column to your table that creates the rank per city.
Rank ColumnRank = RANKX ( FILTER( 'Table', 'Table'[City] = EARLIER('Table'[City]) ), 'Table'[Value], , DESC, DENSE )
Now create a new table that selects only each city entry that was ranked 1.
New table that only has cities that were ranked 1Table2 = SELECTCOLUMNS( FILTER( 'Table', 'Table'[Rank] = 1), "City", 'Table'[City], "Value", 'Table'[Value], "Date", 'Table'[Date] )When you create your table visual now from the values on the new table you will only have the ranked 1st entries.
Comparsion
3 Replies
- amitchandak
Super User
Please try
https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
RMBResolver II
Thought it would be worth mentioning that the first answer will still have all the original values with an extra column identifying the highest for a product type without further work. I'm adding this in case a new table is wanted that only has those highest values and not the whole original table.
First, add a calculated column to your table that creates the rank per city.
Rank ColumnRank = RANKX ( FILTER( 'Table', 'Table'[City] = EARLIER('Table'[City]) ), 'Table'[Value], , DESC, DENSE )
Now create a new table that selects only each city entry that was ranked 1.
New table that only has cities that were ranked 1Table2 = SELECTCOLUMNS( FILTER( 'Table', 'Table'[Rank] = 1), "City", 'Table'[City], "Value", 'Table'[Value], "Date", 'Table'[Date] )When you create your table visual now from the values on the new table you will only have the ranked 1st entries.
Comparsion
