Forum Discussion
Helper IHow 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?
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
6 Replies
mbarrosHi Nick,
I manually entered the STATUS column.
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
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
- If the Type of Sale is "half", then look up if there is another entry in ID column of that company and 2nd half.