Count of unique customers based on set criteria: customer transactions logged on different rows

[Anonymous]

Hi,
I have sales transactions data that lists purchses made by unique customers. Each different item purchased appears against the unique customer ID on a different row (example shown below).

I'm struggling to create a measure for 'Highly Engaged Customer'. I imagine this working like this:
DISTINCTCOUNT of 'customer_id' IF 'membership_sale' is >0 AND 'bottle_sale' is >0 AND 'event_ticket_sale' is >0

Based on my sample dataset, this measure would return a value of 2 (because customer_id '1' and '6' meet these criteria).

Can anyone help please? Thanks in advance.

[v-shex-msft]

Hi @Anonymous ,

You can use following measure with variable to manually summarize all sale fields, then you can simple calculated on summarized table with conditions:

Measure =
VAR merged =
    UNION (
        SELECTCOLUMNS (
            Table,
            "Customer", [Customer_id],
            "type", "membership_sale",
            "sales", SUM ( Table[membership_sale] )
        ),
        SELECTCOLUMNS (
            Table,
            "Customer", [Customer_id],
            "type", "bottle_sale",
            "sales", SUM ( Table[bottle_sale] )
        ),
        SELECTCOLUMNS (
            Table,
            "Customer", [Customer_id],
            "type", "event_ticket_sale",
            "sales", SUM ( Table[event_ticket_sale] )
        )
    )
VAR summary =
    SUMMARIZE (
        merged,
        [Customer],
        "TypeCount", COUNTAX ( FILTER ( merged, [Customer] = EARLIER ( [Customer] ) ), [Type] )
    )
RETURN
    COUNTROWS ( FILTER ( summary, [TypeCount] = 3 ) )

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you @v-shex-msft . This is very helpful and works perfectly.

How might I adapt this to create a similar measure, this time to measure only: 'customer_id' IF 'membership_sale' is >0 AND 'bottle_sale' is >0 AND 'event_ticket_sale' is 0. ?

In the example dataset I previously shared, I would expect a value of 3 to be returned (because customer_id '2' , '3' and '6' meet these criteria).

Once again, many thanks for your assistance.

[vik0810 · Accepted solution]

here is somewhat shorter definition of a measure for your needs:

count =
VAR table_grouped =
    SUMMARIZE (
        'table',
        'table'[customer_id],
        "bottle_total", SUM ( 'table'[bottle_sale] ),
        "membership_total", SUM ( 'table'[membership_sale] ),
        "event_total", SUM ( 'table'[event_ticket_sale] )
    )
RETURN
    COUNTROWS (
        FILTER (
            table_grouped,
            [bottle_total] > 0
                && [membership_total] > 0
                && [event_total] > 0
        )
    )

 Note the FILTER function in the bottom of the measure. Here you can change the defintion of your criteria, i.e. change [event_total] > 0 into [event_total] = 0.

[Anonymous]

Thanks @vik0810 for your assistance.

Likewise, thanks also to @v-shex-msft for your contribution.