topic Getting a single column value based on another (unique) column value - Optimize in DAX Commands and Tips

Getting a single column value based on another (unique) column value - Optimize

Anonymous — Wed, 21 Jul 2021 14:01:57 GMT

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

Re: Getting a single column value based on another (unique) column value - Optimize

amitchandak — Wed, 21 Jul 2021 14:50:46 GMT

Anonymous , I think this is correct. Other way can be

maxx(filter('Date', 'Date'[Date] = Today()), 'Date'[Dashboard_endDate])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Getting a single column value based on another (unique) column value - Optimize

Anonymous — Thu, 22 Jul 2021 10:43:15 GMT

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.