The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to get a distinct count of my customer who purchase a specific product and show all the other product they purchased as well. I tried to put the product name in the filter, but that would not show the other products.
Customer Count = CALCULATE(DISTINCTCOUNT(CustomerKey),'Customers Products'[ProductName]'='Thigh Master'). when I show it in a table it show it like this: Customer Name Product Name
John Doe Thigh Master
but I would like to see: Customer Name Product Name
John Doe Thigh Master
John Doe Ab Roller
John Doe Ab Master
Solved! Go to Solution.
Hi @mosman ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a product name dimension table(DO NOT create any relationship with your fact table)
Products = VALUES('Table'[ProductName])
2. Create a measure as below to determine if the corresponding customer should be displayed
Flag =
VAR _selproduct =
ALLSELECTED ( 'Products'[ProductName] )
VAR _selcustomer =
SELECTEDVALUE ( 'Table'[Customer Name] )
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table'[Customer Name] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ProductName] IN _selproduct )
)
RETURN
IF ( _selcustomer IN _tab, 1, 0 )
3. Create a table visual and apply a filter on this visual with the condition(Flag is 1) just as below screenshot
Best Regards
Hi @mosman ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a product name dimension table(DO NOT create any relationship with your fact table)
Products = VALUES('Table'[ProductName])
2. Create a measure as below to determine if the corresponding customer should be displayed
Flag =
VAR _selproduct =
ALLSELECTED ( 'Products'[ProductName] )
VAR _selcustomer =
SELECTEDVALUE ( 'Table'[Customer Name] )
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table'[Customer Name] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ProductName] IN _selproduct )
)
RETURN
IF ( _selcustomer IN _tab, 1, 0 )
3. Create a table visual and apply a filter on this visual with the condition(Flag is 1) just as below screenshot
Best Regards
any help?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
14 | |
11 | |
3 | |
3 | |
2 |