Forum Discussion
Solved
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. Custome...
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
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
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.