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
mbarros
Helper I
Helper I

How to flag first entry only when second entry occurs?

Hello community!
I'm struggling for days to figure this out, so your help will be greatly appreciated. I'm not able to write code myself from scratch, but I'm relatively good at googling, grabing codes and changing them to my needs.

So here is the situation: my dataset in Power BI have 3 types of sale: one is direct sale, and the other has 2 steps: first half and second half (at a later date, see screenshot below from excel for simplicity, but I need this in Power BI).

Ideally, the calculated measure should result in something like the "STATUS" column.
I need to flag (hide or mark) the first "half" when the "2nd half" happens (e.g. cells E2 and E6).
This is important so I can quickly identify the customers that are pending the 2nd half sale (E5). Makes sense?

 

Capture.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Got it! Even in Power BI it is the same logic, just need to use DAX.

Create ID column by concatenating Company and Type of Sale (add date etc if you want)

Create Status column using this-

Status = IF(Table1[Type of Sale]="half", IF(IFERROR(LOOKUPVALUE(Table1[ID],Table1[ID],CONCATENATE(Table1[Company],"2nd half")),0)>0,"",Table1[Type of Sale]),Table1[Type of Sale])
 
-Lakshmi

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Create another column that is combination of company name and type of sale- New column 'ID' = Concat(B2,C2)  

Example:

DateCompanyType of SaleAmountStatusID
1/1/2019Business Ahalf5,000 Business Ahalf

 

Then use this formula on Status column

=IF(C2="half", IF(IFERROR(VLOOKUP(CONCATENATE(B2,"2nd half"),$E$2:$E$1048576,1,FALSE), 0)>0,"",C2),C2)

For every row, this formula checks-

  • If the Type of Sale is "half", then look up if there is another entry in ID column of that company and 2nd half. 
    • If Company 2nd half exists, then status will be changed to ""
    • If Company 2nd half doesn't exist, status will be same as Type of Sale
  • If the Type of Sale is not "half", status will be same as Type of Sale

Hope this helps. Cheers!

-Lakshmi

 

Thanks for your reply lakshmis.

I'm sorry if my first post was confusing (I just edited). Actually, I need this formula in Power BI. I created this example on Excel just to simplify. and make it easier for you guys to understand what I meant.

Anonymous
Not applicable

Got it! Even in Power BI it is the same logic, just need to use DAX.

Create ID column by concatenating Company and Type of Sale (add date etc if you want)

Create Status column using this-

Status = IF(Table1[Type of Sale]="half", IF(IFERROR(LOOKUPVALUE(Table1[ID],Table1[ID],CONCATENATE(Table1[Company],"2nd half")),0)>0,"",Table1[Type of Sale]),Table1[Type of Sale])
 
-Lakshmi

Thanks so much for your time and effort, lakshmis

Worked like a charm, you r the best !

Anonymous
Not applicable

Is the Status column in Excel a formula? Or manually input?

Hi Nick,
I manually entered the STATUS column.

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.