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

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors