Dax Measure at least one product

[mosman]

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

[v-yiruan-msft · Accepted 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

[mosman]

any help?