Hi all, I need help with using FIRSTNONBLANK() sorting a string column by date. Unfortunately, when I sort it by the string, it returns me the min(string) because A is lower than B and B lower than C so it will always sort alphabetically and give me first row by alphabet. As shown below, for CustomerID = 1, two products were returned (Apples and Bananas) on 1/2/2021 and 1/5/2021 respectively, it is showing me Apples as the FirstReturnProduct because alphabetically, it is lower than Bananas. However, we can see that Bananas was returned before Apples and I want to be showing Bananas in the FirstReturnProduct column for CustomerID = 1. The same applies for CustomerID = 2 where it displayes Oranges instead of Peaches as the FirstReturnProduct because O < P but I want to be showing peaches since it had an earlier Return Date. CustomerIDPurchaseTransactionTypeTransactionDateFirstReturnProduct1BananasNULL1/1/2021Apples1BananasReturn1/2/2021Apples1BananasNULL1/3/2021Apples1ApplesNULL1/4/2021Apples1ApplesReturn1/5/2021Apples2PeachesNULL1/6/2021Oranges2PeachesReturn1/7/2021Oranges2OrangesReturn1/8/2021Oranges2OrangesNULL 1/9/2021Oranges2OrangesNULL1/10/2021Oranges Below is what I want to get CustomerIDPurchaseTransactionTypeTransactionDateFirstReturnProduct1BananasNULL1/1/2021Bananas1BananasReturn1/2/2021Bananas1BananasNULL1/3/2021Bananas1ApplesNULL1/4/2021Bananas1ApplesReturn1/5/2021Bananas2PeachesNULL1/6/2021Peaches2PeachesReturn1/7/2021Peaches2OrangesReturn1/8/2021Peaches2OrangesNULL 1/9/2021Peaches2OrangesNULL1/10/2021Peaches Any help would be appreciated.

Hi sanaankbar56,I was very clear with your requirement and I have tested it as below. from above table, the "FirstRetnFruit" is the measure for for it.The code is as I showed at my previous answer. Hope this helps you. Thanks

Use FIRSTNONBLANK() with String and Date | Microsoft Fabric Community

Forum Discussion

Related content

firstnonblank

Using FIRSTNONBLANK unsuccessfully.

Filter on FIRSTNONBLANK

Doubts with FIRSTNONBLANK

firstnonblank issue