DAX to flag Customer as Yes, No, or Both based on purchase date from Sales Table

[jesspiorier]

I'm struggling with a dax equation to identify different types of customers, specifically a Yes, No, or Both scenario for holiday shoppers.

I have a transaction table and a customer table (Sales and Cust tables both have an ID#). 

I've flagged Transactions as a Yes or No, based on Purchase Month. (Holiday Shoppers=10, 11, 12, Non-Holiday= 1, 2, 3, 4, 5, 6, 7, 8, 9).  How do I identify customers who are exclusively Yes or No, or those who are both?  

I found a post that looked at "One-Time", "First Order" and "Returned", but the DAX was based on Min/Max purchase dates and I can't figure out how to customize it for my needs.

Sample:

Transaction #	Customer ID	Purchase Month	Holiday Purchase
123	A	6	N
124	B	7	N
125	C	8	N
126	D	9	N
127	E	10	Y
128	A	10	Y
129	A	11	Y
130	B	12	Y
131	C	1	N
132	D	2	N
133	D	3	N

Desired Reesults:

Customer ID	Holiday Shopper
A	Both
B	Both
C	No
D	No
E	Yes

Thanks in advance.  -Jess

[v-lid-msft · Accepted solution]

Hi @jesspiorier ,

Solution using measure and table visual is great, but in case you want a calculated table, you can use the following formula.

Table 2 = 
ADDCOLUMNS (
    DISTINCT ( 'Table'[Customer ID] ),
    "Holiday Shopper",
    VAR t =
        FILTER ( 'Table', 'Table'[Customer ID] = EARLIER ( [Customer ID] ) )
    VAR holiday =
        COUNTROWS ( FILTER ( t, [Holiday Purchase] = "Y" ) ) + 0
    VAR noholiday =
        COUNTROWS ( FILTER ( t, [Holiday Purchase] = "N" ) ) + 0
    RETURN
        SWITCH (
            TRUE (),
            holiday > 0
                && noholiday > 0, "Both",
            holiday > 0
                && noholiday <= 0, "Yes",
            "No"
        )
)

Or you can add a Calculated column in your customer table:

Holiday Shopper =
VAR id = [Customer ID]
VAR t =
    FILTER ( 'Table', 'Table'[Customer ID] = id )
VAR holiday =
    COUNTROWS ( FILTER ( t, [Holiday Purchase] = "Y" ) ) + 0
VAR noholiday =
    COUNTROWS ( FILTER ( t, [Holiday Purchase] = "N" ) ) + 0
RETURN
    SWITCH (
        TRUE (),
        holiday > 0
            && noholiday > 0, "Both",
        holiday > 0
            && noholiday <= 0, "Yes",
        "No"
    )

And here is my measure:

Holiday Shopper = 
VAR holiday =
    COUNTROWS ( FILTER ( 'Table', 'Table'[Holiday Purchase] = "Y" ) ) + 0
VAR noholiday =
    COUNTROWS ( FILTER ( 'Table', 'Table'[Holiday Purchase] = "N" ) ) + 0
RETURN
    SWITCH (
        TRUE (),
        holiday > 0
            && noholiday > 0, "Both",
        holiday > 0
            && noholiday <= 0, "Yes",
        "No"
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jesspiorier]

Thanks again @v-lid-msft . This worked perfectly.  

However, now I'm trying to do it for clients who fall into four different categories, or a combination of them.  I tried modifying your original "if then else" formula, but I'm not getting the expected results.  Perhaps you can help?

It's essentially the same issue.  One customer who has had at least one interaction (potentially many). Those interactions can fall into four categories (not the original post's two).  It is NOT time-related, nor do I want results at the customer ID level.  I want a Distinct Count of IDs based on Category Distribution.

It is a single flat table. Client ID is unique. Each interaction (row) has the Category flag.

I can get a Distinct Count of IDs per Category easily.  I want to know how many IDs fit into one Category only, or several.  Expected result:

Category A Only:  5

Category B Only: 2

Category C Only: 1

Category D Only: 4

AB -   3

AC  - 2

AD - 1

BC -2

BD - 0

CD - 5

ABC: 0

ABD: 1
BCD: 3

CDA: 1
ABCD: 10

Does that make sense?  Is it possible with DAX?  

[v-lid-msft]

Hi @jesspiorier ,

Could you please share the sample data based on the expected table you have shared? 

Please don't have any Confidential Information or Real data in your reply.

Best regards,

[jesspiorier]

Thank you @v-lid-msft . The calculated column (your second suggestion) gave me the expected results.  Thanks for the help!  

Note: I did need to change the names of the variables you suggested in your example.  I was getting a syntax error on "id" and "t" for some reason.  So I called them "Customer Count" and "Truth". Worked just fine.  Thanks again.

Holiday Shopper =
VAR CustomerCount = 'Sales'[Customer ID]
VAR truth =
    FILTER ( 'Sales', 'Sales'[Customer ID] = CustomerCount )
VAR holiday =
    COUNTROWS ( FILTER ( truth, [Holiday Purchase] = "Y" ) ) + 0
VAR noholiday =
    COUNTROWS ( FILTER ( truth, [Holiday Purchase] = "N" ) ) + 0
RETURN
    SWITCH (
        TRUE (),
        holiday > 0
            && noholiday > 0, "Both",
        holiday > 0
            && noholiday <= 0, "Yes",
        "No"
    )

Based on some additional equations I have used, the results are as expect.

[ChrisMendoza]

@jesspiorier -

Does the following accomplish your task?

Measure 2 =
VAR _holidayYes =
    CALCULATE (
        COUNTROWS ( TableName ),
        TableName[Holiday Purchase] = "Y"
    )
VAR _holidayNo =
    CALCULATE (
        COUNTROWS ( TableName ),
        TableName[Holiday Purchase] = "N"
    )
RETURN
    SWITCH (
        TRUE (),
        _holidayNo >= 1
            && _holidayYes >= 1, "Both",
        _holidayNo >= 1, "No",
        "Yes"
    )

[jesspiorier]

Hi @ChrisMendoza . Thanks for the prompt response.  Unfortunately, the measure did not work as expected.  Even though I see Y and N flags in my dataset (and I know that there are multiple one-time or repeat shoppers), the only value returned was "both".  I used the calculated column suggested below and it worked.  Thank you though.

[Nathaniel_C]

Hi @jesspiorier ,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Shopper Timing = 

var Holiday = CALCULATE(COUNTA(Customer[Holiday Purchase]),FILTER(ALLEXCEPT(Customer,Customer[Customer ID]), Customer[Holiday Purchase] = "Y" ))
var Othermonth =  CALCULATE(COUNTA(Customer[Holiday Purchase]),FILTER(ALLEXCEPT(Customer,Customer[Customer ID]), Customer[Holiday Purchase] = "N" ))

var Whichmonths = If(Holiday>0 && Othermonth>0,"Both", If(Holiday>0 && Othermonth=0,"Yes",  IF(Holiday=0 && Othermonth>0,"No","N/A")))



return Whichmonths

[Nathaniel_C]