Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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-
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-
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-
Thanks so much for your time and effort, lakshmis
Worked like a charm, you r the best !
Is the Status column in Excel a formula? Or manually input?
Hi Nick,
I manually entered the STATUS column.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |