Customer Segmentation - based on order rank 1 or rank 2

[Andrew_na_lang]

Hi,

I need help! 

So, in my orders table, I created two calculated columns - 

_____Redeemer 1 =

VAR CustomerID = orders[customer_id]

VAR _HC = orders[with HC]

RETURN

IF( CustomerID = orders[customer_id] &&

    _HC = "Yes" &&

    orders[_Order Ranking] = 1 ,

    "REDEEMER" ,

    ""

   )

and

_____Redeemer 2 =

VAR CustomerID = orders[customer_id]

VAR _HC = orders[with HC]

RETURN

IF( CustomerID = orders[customer_id] &&

    _HC = "Yes" &&

    orders[_Order Ranking] = 2 ,

    "REDEEMER" ,

    ""

   )


What I am trying to achieve here is: 

1. Identify which order the customer sent an HC bag- in the first or second order. 
2. Then, marked the unique customer REDEEMER1, REDEEMER2, NON-REDEEMER
REDEEMER1 is if he/she submitted an HCbag in the first order or REDEEMER1 
REDEEMER2 is if he/she submitted an HCBag in the second order but no HC bag in the first order.
Lastly, if no HC bag in both the first or second order, Tag him as "NON-REDEEMER".

Ultimately, I need to create a stacked column chart where my x-axis is the order ranking, the y-axis is the distinct count of customer_id, having a legend REDEEMER1, REDEEMER2, and NON-REDEEMER.  In the chart, REDEEMER2 should only appear from order_rank 2 onwards, there shouldn't be any REDEEMER2 in Order rank 1. 

For some reason, I cannot add the pbix file here. I can share it to anyone who is willing to help me out. THank you.

[Andrew_na_lang]

@amitchandak here is a link to google sheets - https://docs.google.com/spreadsheets/d/13smuzNornBBnk2-_utR1WdJjLzZBlvWvRjYxJqoFwiw/edit#gid=1387572...

[amitchandak]

@Andrew_na_lang , Try a new column like

var _max = maxx(filter(orders, orders[customer_id] = earlier(orders[customer_id]) && orders[with HC] ="Yes" ), orders[_Order Ranking])
return
Switch(true(),
_max =1, "REDEEMER 1",
_max =2, "REDEEMER 2",
"NON-REDEEMER")

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Andrew_na_lang]

@amitchandak it didn't work. For some reason, I cannot paste the pbix file here. How can I share it to you?

[Andrew_na_lang]