Forum Discussion

mosman's avatar
mosman
Frequent Visitor
4 years ago
Solved

Dax Measure at least one product

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

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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