Forum Discussion
Getting a single column value based on another (unique) column value - Optimize
Hi all,
I'm trying to get a single column value from a table based on another (unique) column value. I've managed to get the value, but I have the feeling it's too clunky and could be made way simpler. Since my objective is not just to get something to work, but also to learn as much about DAX as I can, I figured I'd ask the community for help.
In a nutshell; I'm looking for a value in a (calculated) column in a date table. I need the value for Today. This seems to work, but feels too complex to just return a single value. Any advice would be appreciated. Thanks!
VAR endDate = CALCULATE( MAX( 'date'[dashboard_endDate] ), 'date'[date] = TODAY() )
2 Replies
Anonymous , I think this is correct. Other way can be
maxx(filter('Date', 'Date'[Date] = Today()), 'Date'[Dashboard_endDate])
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thanks for the reply, amitchandak!
The statement works indeed as expected. I'm just looking to see if I can optimize it. I find it weird I have to use a calculate' and 'MAX' (or maxx), if I just want to return a single value from a table where I know I'm not looking at a range of data but just a single row.
Sample data is very basic. It looks something like this. I just want to get the corresponding 'dashboard_endDate' for the current date so that I can automatically update data in my dashboard. (I can't use a relative date slicer for example, because information of the previous month can only be shown 'x' working days after the new month starts).
Date dashboard_endDate 2021-06-01 2021-04-30 2021-06-02 2021-04-30 2021-06-03 2021-04-30 2021-06-04 2021-05-31 2021-06-05 2021-05-31 2021-06-06 2021-05-31 This might just be the way PowerBI/DAX works and I'm overthinking things. If that's the case, I'll stop looking for another solution.
