How to Summarise VAR table - customer purchase analysis

[anandav]

Hi All,

I have a standard star schema of Sales, Products and Customers

I wanted to count how many customers have bought products ONLY in ONE category for the given period.

e.g. C1 and C2

I have a measure

1. Summarize sales by Customer and Category ID

VAR T1 = 
ADDCOLUMNS(
            SUMMARIZE(
                FILTER(ALL(Sales), Sales[SaleDate] >= DATE(2021, 01,01) )
                ,Customers[Cust ID]
                ,Products[Category ID]
            )
            ,"CatCount" , 1
        )

Now I wanted another VAR table T2 that will add CatCount by Customer 

My thinking is by filtering T2 where CategoryID = "PC1"  && CatCount = 1 should give me customers who have ONLY bought in Product Category PC1

Any suggestion how I can do this please?

[amitchandak]

@anandav , Try a measure like

countx(filter(
SUMMARIZE(
FILTER(ALL(Sales), Sales[SaleDate] >= DATE(2021, 01,01) )
,Customers[Cust ID]
, "_Count", distinctCOUNT(Products[Category ID])
)
,[_Count] =1
), [Cust ID])

[anandav]

@amitchandak ,

Thank you for the reply.

Using the above DAX I am getting the same value for all Product Categories.

The PBI file is here.

What I need is the Categories where customers have ONLY purchased in that category.

Expected results based on attached PBI file is:

Hope I have explained the requirement clearly.

[v-rzhou-msft · Accepted solution]

Hi @anandav 

You can try the measure as  below.

Measure:

_Category Only Customers =
VAR _T =
    FILTER (
        ADDCOLUMNS (
            Sales,
            "COUNT_",
                CALCULATE (
                    DISTINCTCOUNT ( Products[Product Category] ),
                    FILTER ( ALL ( Sales ), Sales[Customer] = EARLIER ( Sales[Customer] ) )
                )
        ),
        [COUNT_] = 1
            && [SaleDate] >= DATE ( 2021, 01, 01 )
    )
VAR _T2 =
    SUMMARIZE ( _T, [Customer] )
RETURN
    COUNTX ( _T2, [Customer] )

Result is as below.

 

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[anandav]

@v-rzhou-msft 

Thanks a lot. That works.

Really appreciate your help. 

Ps. Sorryfor the late reply - off work due to some issues.