cancel
Showing results for
Did you mean:
Frequent Visitor

## Customer SCD2 Activity over Current & Old Records

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?

1 ACCEPTED SOLUTION
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Microsoft

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

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

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.

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!