Forum Discussion
Flag rows based on conditions
Hi Everyone,
I have a set of data as shown below,
| Customer | Invoicedate | Event |
| A | 1/8/2023 | Trial |
| A | 4/8/2023 | Purchase |
| B | 1/8/2023 | Purchase |
| B | 3/8/2023 | Exchange |
| B | 4/8/2023 | Demo |
| C | 1/8/2023 | Purchase |
| C | 2/8/2023 | Exchange |
| C | 3/8/2023 | Trial |
| D | 1/8/2023 | Trial |
| D | 2/8/2023 | Purchase |
| D | 3/8/2023 | Exchange |
| D | 4/8/2023 | Purchase |
| E | 1/8/2023 | Demo |
| E | 4/8/2023 | Purchase |
I need to create a new column say "IncludeFlag". This column should contain a flag based on the criterias as given below.
1) If the Customer's first Event is Trial or Demo then "1" should be flagged to every row of the customer.
2) "0" to be flagged incase the Event is not Trial or Demo or if the first event is not Trial or Demo.
Below is the desired output.
| Customer | Invoicedate | Event | IncludeFlag |
| A | 1/8/2023 | Trial | 1 |
| A | 4/8/2023 | Purchase | 1 |
| B | 1/8/2023 | Purchase | 0 |
| B | 3/8/2023 | Exchange | 0 |
| B | 4/8/2023 | Demo | 0 |
| C | 1/8/2023 | Purchase | 0 |
| C | 2/8/2023 | Exchange | 0 |
| C | 3/8/2023 | Trial | 0 |
| D | 1/8/2023 | Trial | 1 |
| D | 2/8/2023 | Purchase | 1 |
| D | 3/8/2023 | Exchange | 1 |
| D | 4/8/2023 | Purchase | 1 |
| E | 1/8/2023 | Demo | 1 |
| E | 4/8/2023 | Purchase | 1 |
Please suggest a way in which, I can achieve this.
Thank you and Regards
pls try this
Flag = VAR _t1 = [Customer] VAR _MinDate = MINX(FILTER(ALL('Table'),'Table'[Customer]=_t1),[Invoicedate]) VAR _tbl = FILTER(ALL('Table'),'Table'[Customer]=_t1&&'Table'[Event]="Trial"&&[Invoicedate]=_MinDate) RETURN COUNTROWS(_tbl)If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
4 Replies
pls try this
Flag = VAR _t1 = [Customer] VAR _MinDate = MINX(FILTER(ALL('Table'),'Table'[Customer]=_t1),[Invoicedate]) VAR _tbl = FILTER(ALL('Table'),'Table'[Customer]=_t1&&'Table'[Event]="Trial"&&[Invoicedate]=_MinDate) RETURN COUNTROWS(_tbl)If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a lot, works as per my requirement.
can you please tell me what is
VAR _t1 = [Customer]
?
