Forum Discussion
How to flag first entry only when second entry occurs?
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
Create another column that is combination of company name and type of sale- New column 'ID' = Concat(B2,C2)
Example:
| Date | Company | Type of Sale | Amount | Status | ID |
| 1/1/2019 | Business A | half | 5,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.
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