How to get a column value from the first row in a sorted table?

[Anonymous]

Hi,

I am trying to get the first non-blank value from a table that has been sorted in the query editor. The sorting is crucial to which row is first in the table.

I have searched many posts and understand that FIRSTNONBLANK follows this rule. "It's important to remember that the order you see the data in the tables does not really matter " when using FIRSTNONBLANK, it acts as a MIN in the measurement context.

Data:

Resource	DateTime	SL38 TimeDifference
SL38	11/12/2019 12:30:00 PM	5
SL38	11/12/2019 11:48:00 PM	10
SL38	11/11/2019 6:12:00 PM	0
SL38	11/11/2019 1:57:00 AM	50
SL40	....	....
....	....	....

Proposed Solution #1 Measurement:

SL38 Recent Time = 
CALCULATE(
    FIRSTNONBLANK('Molding'[SL38 TimeDifference], 'Molding'[SL38 TimeDifference]), 
    // Sort the rows by descending DateTime
    Filter('Molding', 'Molding'[Resource] = "SL38")
    )

Results: 0

Desired Results: 5 (The most recent DateTime)

First Proposed Method: Use FIRSTNONBLANK and somehow sort the columns in the measurement by DateTime.

Second Proposed Method: Use TOPN to retrieve the first row and somehow get the Time Difference column from it.

Third Proposed Method: Use LOOKUPVALUE to search by the most recent date.  The most recent date happens multiple times in the DateTime column so it somehow needs to filter by Resource.

Starter Code for #2:

SL38 Recent Time = 
CALCULATE(
    TOPN(1, 'Molding', 'Molding'[DateTime], 0), // Get the value for the SL38 Time Difference column
    Filter('Molding', 'Molding'[Resource] = "SL38")
    )

Starter Code for #3:

SL38 Recent Time = 
CALCULATE(
    LOOKUPVALUE(Molding[SL38 TimeDifference], Molding[DateTime], LASTNONBLANK('Molding'[DateTime], 'Molding'[DateTime])) // Filter by Resource, 
    Filter('Molding', 'Molding'[Resource] = "SL38")
    )

Any help is appreciated.

[parry2k]

@Anonymous once you sorted your data in power query, you should add a index column and then from there it will be much easier to get the value of first row.

[Anonymous]

@parry2k I have an index column. However, since my SQL query is connecting 6 different resources, only 1 resource has the 0th index.

Do you know of a method to get the column value (TimeDifference) from the row with the minimum index of that resource?

[parry2k]

@Anonymous is it possible to share sample data in excel sheet and I will get you the solution. I think that will be much quicker.

[Anonymous]

@parry2k Here is some sample data.

https://www.dropbox.com/s/7af01mk4z7tbjey/Sample%20Data.xlsx?dl=0

[Anonymous · Accepted solution]

@parry2k I solved the question by doing the following code. For some reason, even though LASTNONBLANK and MAX on DateTime returned the exact same date, MAX was the one that worked correctly.

SL38 Recent Time = 
CALCULATE(
    MAX('Molding'[SL38 TimeDifference]), 
    FILTER('Molding', 'Molding'[DateTime] = MAX('Molding'[DateTime])),
    FILTER('Molding', 'Molding'[Resource] = "SL38")
    )

[unais]

please help on https://community.powerbi.com/t5/Desktop/Stock-Aging-Report/m-p/960534#M460215