topic Get First Value From A Column in DAX Commands and Tips

Get First Value From A Column

leolapa_br — Fri, 01 Nov 2024 18:11:31 GMT

Hi have a table called "StockQuotes" that looks like this:

I wrote the following code as an attempt to fetch the first available (from the earliest date) Close Price for each respective stock ticker:

First Close = VAR Ticker = SELECTEDVALUE( StockQuotes[Ticker] ) VAR Result = MINX ( TOPN ( 1, FILTER ( StockQuotes, StockQuotes[Ticker] = Ticker ), StockQuotes[Date], ASC ), StockQuotes[Close Price] ) RETURN Result

I then created the matrix visual below with the dates along the rows and stock tickers across the top to check whether the above code produced the desired results:

But as it can be seen the code it's not working since I was expecting the matrix to return repeated numbers under the First Close column for each respective ticker (i.e.: $63.96 for AAPL, $89.57 for AMZN, $323,400.00 for BRK-A, $63.69 for GOOG and so on...), not a mirror image of the Close Price column.

What am I missing here?

Any help on getting to the right code is greatly apreciated!

Re: Get First Value From A Column

leolapa_br — Fri, 01 Nov 2024 18:46:06 GMT

ChatGTP came to the rescue here and produced the following code that solved the issue:

First Close = CALCULATE( FIRSTNONBLANK(StockQuotes[Close Price], 1), FILTER( ALL(StockQuotes), StockQuotes[Ticker] = SELECTEDVALUE(StockQuotes[Ticker]) && StockQuotes[Date] = CALCULATE( MIN(StockQuotes[Date]), ALLEXCEPT(StockQuotes, StockQuotes[Ticker]) ) ) )

Re: Get First Value From A Column

Anonymous — Mon, 04 Nov 2024 01:19:47 GMT

Hi leolapa_br ,

Thanks for sharing, this will help other users who have the same question.

Best regards,

Mengmeng Li