Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |