Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sananakbar56
Regular Visitor

Use FIRSTNONBLANK() with String and Date

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. 

 

CustomerIDPurchaseTransactionTypeTransactionDateFirstReturnProduct
1BananasNULL1/1/2021Apples
1BananasReturn1/2/2021Apples
1BananasNULL1/3/2021Apples
1ApplesNULL1/4/2021Apples
1ApplesReturn1/5/2021Apples
2PeachesNULL1/6/2021Oranges
2PeachesReturn1/7/2021Oranges
2OrangesReturn1/8/2021Oranges
2OrangesNULL 1/9/2021Oranges
2OrangesNULL1/10/2021Oranges

 

Below is what I want to get

 

CustomerIDPurchaseTransactionTypeTransactionDateFirstReturnProduct
1BananasNULL1/1/2021Bananas
1BananasReturn1/2/2021Bananas
1BananasNULL1/3/2021Bananas
1ApplesNULL1/4/2021Bananas
1ApplesReturn1/5/2021Bananas
2PeachesNULL1/6/2021Peaches
2PeachesReturn1/7/2021Peaches
2OrangesReturn1/8/2021Peaches
2OrangesNULL 1/9/2021Peaches
2OrangesNULL1/10/2021Peaches

 

Any help would be appreciated. 

 

 

 

1 ACCEPTED SOLUTION
colacan
Resolver II
Resolver II

Hi sanaankbar56,

I was very clear with your requirement and I have tested it as below.

 

colacan_1-1632601344833.png

 

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

View solution in original post

5 REPLIES 5
colacan
Resolver II
Resolver II

Hi sanaankbar56,

I was very clear with your requirement and I have tested it as below.

 

colacan_1-1632601344833.png

 

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

It worked now. We were missing one filter. 

 

FirstReturnProduct =
           CALCULATE(
                      FIRSTNONBLANKVALUE(
                            Fruits[TransactionDate],
                            SELECTEDVALUE( fruits[Purchase] )
                      ),
                     ALLEXCEPT( Fruits, Fruits[CustomerID] ), Fruits[TransactipnType]  = "Return"
           )
 
Thanks a lot for your help. I have accepted this as solution. 

Thankyou for responding. Yes, that brings me very close but I think this is not factoring in the Transaction Type column. Currently, it is taking the first product purchased by each customer and returning that. What I am looking for is the first time there was a Return flag (Transaction Type = Return ) for each customer. 

 

I think another dataset which represents this problem is below and the FirstReturnProduct Column is what I want. 

 

CustomerIDPurchaseTransactionTypeTransactionDateFirstReturnProduct
1MelonsNULL1/1/2021Bananas
1BananasReturn1/2/2021Bananas
1BananasNULL1/3/2021Bananas
1ApplesNULL1/4/2021Bananas
1ApplesReturn1/5/2021Bananas
2AvocadoNULL1/6/2021Peaches
2PeachesReturn1/7/2021Peaches
2OrangesReturn1/8/2021Peaches
2OrangesNULL 1/9/2021Peaches

 

 

 

colacan
Resolver II
Resolver II

Hi sananakbar56,

 

Please try below code:

 

FirstReturnProduct =
           CALCULATE(
                      FIRSTNONBLANKVALUE(
                            Fruits[TransactionDate],
                            SELECTEDVALUE( fruits[Purchase] )
                      ),
                     ALLEXCEPT( Fruits, Fruits[CustomerID] )
           )
 
 
Hope this helps you. Thanks

Thankyou for responding. Just to clarify it more, I am looking for cases where the Transaction Type is Return. For example, the earliest return for CustomerID = 1 was 1/2/2021 and the Product was Bananas which is then supposed to appear on the last column. I am looking to get the First Product Returned for each customer ID. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors