Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
CustomerID | Purchase | TransactionType | TransactionDate | FirstReturnProduct |
1 | Bananas | NULL | 1/1/2021 | Apples |
1 | Bananas | Return | 1/2/2021 | Apples |
1 | Bananas | NULL | 1/3/2021 | Apples |
1 | Apples | NULL | 1/4/2021 | Apples |
1 | Apples | Return | 1/5/2021 | Apples |
2 | Peaches | NULL | 1/6/2021 | Oranges |
2 | Peaches | Return | 1/7/2021 | Oranges |
2 | Oranges | Return | 1/8/2021 | Oranges |
2 | Oranges | NULL | 1/9/2021 | Oranges |
2 | Oranges | NULL | 1/10/2021 | Oranges |
Below is what I want to get
CustomerID | Purchase | TransactionType | TransactionDate | FirstReturnProduct |
1 | Bananas | NULL | 1/1/2021 | Bananas |
1 | Bananas | Return | 1/2/2021 | Bananas |
1 | Bananas | NULL | 1/3/2021 | Bananas |
1 | Apples | NULL | 1/4/2021 | Bananas |
1 | Apples | Return | 1/5/2021 | Bananas |
2 | Peaches | NULL | 1/6/2021 | Peaches |
2 | Peaches | Return | 1/7/2021 | Peaches |
2 | Oranges | Return | 1/8/2021 | Peaches |
2 | Oranges | NULL | 1/9/2021 | Peaches |
2 | Oranges | NULL | 1/10/2021 | Peaches |
Any help would be appreciated.
Solved! Go to Solution.
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
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
It worked now. We were missing one filter.
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.
CustomerID | Purchase | TransactionType | TransactionDate | FirstReturnProduct |
1 | Melons | NULL | 1/1/2021 | Bananas |
1 | Bananas | Return | 1/2/2021 | Bananas |
1 | Bananas | NULL | 1/3/2021 | Bananas |
1 | Apples | NULL | 1/4/2021 | Bananas |
1 | Apples | Return | 1/5/2021 | Bananas |
2 | Avocado | NULL | 1/6/2021 | Peaches |
2 | Peaches | Return | 1/7/2021 | Peaches |
2 | Oranges | Return | 1/8/2021 | Peaches |
2 | Oranges | NULL | 1/9/2021 | Peaches |
Hi sananakbar56,
Please try below code:
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |