cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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?

 

Thanks in advance.

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @catfood49,

 

Suppose you have created relationships between these three tables.

1.PNG

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.

2.PNG

 

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.

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @catfood49,

 

Suppose you have created relationships between these three tables.

1.PNG

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.

2.PNG

 

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.

View solution in original post

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
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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors