Being a total rookie, I am unsure if there is a setting/toggle I can flick to get at this, but I am trying to display a 'card' with the max value AND the date of that value. Getting the MAX value is easy, of course, but can't figure out how to get the date of that value...assuming I would use two cards and overlay them to achieve the style I am going for, but just not sure of the DAX necessary to get the date.
In Excel, I would use MATCH/OFFSET or vlookups to find the max value, offset to get the corresponding date header, and use that value. In PowerBI, I am clueless.
So I have a card that shows the max value of the 'Total U.S. Exports' column; for the timeperiod in the screenshot, it would display "$86,192". I would then like to show the date of this 'record-setting/highest' value.
Is there an inherent measure that gets at this, or do I need to seek a little DAX to do this?
Solved! Go to Solution.
If I could ask a follow-up, as the FIRSTNONBLANK usage just hit me; this works ONLY because I have the date/period in the first column, correct? If the period were in the middle of all the other columns, this approach wouldn't work?
(I'll play with that now, but thought I would ask in hopes of better understanding some of these functions while I wait for a book on DAX to arrive). 🙂
Not really, no. The first part of the measure
TOPN ( 1, VALUES ( 'Table'[Period] ), CALCULATE ( SUM ( 'Table'[Total US Exports] ) ) )
Gives us the top values from a table based on an expression.
How many values? 1
What table? VALUES('Table'[Period]) gives us a table of the unique items in the [Period] column.
What expression? SUM('Table'[Total US Exports]).
Thank you. Thank you. Thank you!
It was a long road to get there, but once I followed your example more closely, I got it to work. 🙂
I originally had...
...and it was returning a month (albeit the wrong month), so I tried to add a compound value to get the month and the year, but no luck.
So I created a new measure to return the year and it worked fine. HOWEVER, I then noticed the [period].[month] was returning the WRONG month! I could not figure out why, but finally looked more closely as your example, removed the [month] from the code, and all was well! I got the month AND year with one measure now....good to go.
I don't understand why the use of 'FIRSTNONBLANK', but I won't question it...and 'FIRSTNONBLANK' will solve another probablem I have been thinking about (how to return the last/most recent value, so charts update and reflect the latest data without me going in and changing the filter to the last month for which I have data).
The outlined portion of the measure returns a table of values. In this case it is a table with 1 row but DAX doesn't care and gives the error 'a table of multiple values was supplied..' FIRSTNOBLANK converts that into a scalar value based on the expression, in this case just 1 so it just gives us the single row in the outlined table.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.