Customer SCD2 Activity over Current & Old Records

[catfood49]

Hi all

I have a Customer Dimension table (SCD2), a Sales Fact table, and Product Dimension table:

I want to do analysis on users who have bought both a Shirt, and some Shorts.

Customer ID A bought both a Shirt and Shorts but as the Dimension record changed over time the Customer Keys are recorded are 1 and 4.

What Measure(s) would I need to do to capture that they have bought both?

Thanks in advance.

[v-yulgu-msft · Accepted solution]

Hi @catfood49,

Suppose you have created relationships between these three tables.

Create a calculated table. (in my test, it's named as Table1) Also, in this table, add a new calculated column.

Table1 =
FILTER (
    SELECTCOLUMNS (
        'Fact Sale',
        "Customer Name", RELATED ( 'Dim Customer'[Customer ID] ),
        "Product Name", RELATED ( 'Dim Product'[Site Name] )
    ),
    [Product Name] = "Shirt"
        || [Product Name] = "Shorts"
)

Count =
CALCULATE (
    DISTINCTCOUNT ( Table1[Product Name] ),
    ALLEXCEPT ( Table1, Table1[Customer Name] )
)

Based on Table1, create an other calculated table.

Table2 = FILTER(Table1,Table1[Count]=2)

You can get below result.

Regards,
Yuliana Gu

[catfood49]

Thats great, @v-yulgu-msft.

I'm tying what I asked into some other things so don't have everything I need yet but this has definitely got me going.

Thanks very much.

[Greg_Deckler]

Depends on what you want to do with it, but here would be one way to identify those customers.

Create the following measures:

CountOfShirts = CALCULATE(COUNTROWS('Fact Retail Sale'),FILTER('Fact Retail Sale','Fact Retail Sale[Product Key]=1))

CountOfShorts = CALCULATE(COUNTROWS('Fact Retail Sale'),FILTER('Fact Retail Sale','Fact Retail Sale[Product Key]=3))

Put these measures into a table along with Customer ID. Filter CountOfShirts and CountOfShorst to > 0