DAX measure to show products that were sold exclusively to the selected customers and no one else

[developer-islan]

Hi everyone,

I'm working to create a DAX measure [Exclusive Product] to show products that were sold exclusively to the selected customers, and to no one else.

If I filter to show only certain customers within a certain date range, I want to show the products that were sold just to the selected customers.  Products that were also sold to other customers during that time will be excluded.

I've linked to a .pbix with example data and a first attempt (not-yet-working) at the measure below.

Thank you for your help!

• Exclusive Product DAX Measure.pbix 

[v-deddai1-msft · Accepted solution]

Hi @developer-islan ,

You can use the following dax measure:

Exclusive Product1 =
VAR A =
    CALCULATETABLE (
        VALUES ( Sales[Product] ),
        FILTER (
            ALL ( Customers ),
            NOT ( Customers[Customer ID] IN VALUES ( Customers[Customer ID] ) )
        ),
        REMOVEFILTERS ( Products )
    )
VAR B =
    CALCULATETABLE ( VALUES ( Sales[Product] ), REMOVEFILTERS ( Products ) )
RETURN
    IF (
        ISFILTERED ( Customers[Customer ID] ),
        IF ( MAX ( Products[Product] ) IN EXCEPT ( B, A ), 1, 0 ),
        0
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@developer-islan , Try a meausre like this and put in visual with product id

measure =
var _cnt = countrows(allselected(customers[Customer ID]))
return
countx(filter(summarize(Sales, products[product ID], "_1", calculate(distinctCOUNT(customers[Customer ID]), all(sales)), "_2", distinctCOUNT(customers[Customer ID]))
,[_1] =_cnt && [_2] =[_1]),[product ID])

in case this did not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[developer-islan]

Hi @amitchandak 

Thank you for your feedback.  This doesn't seem to work as intended.  I've put together the following sample .pbix with the measure and sample data.  Filtering by Customer doesn't show the correct exclusive products.

• Exclusive Product DAX Measure.pbix 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  It is not a perfect solution but good enough to get you started.  In the screenshot below, you can see that P4 was bought only by Customer 1.  When C1 and C2 is selected in the slicer, P3 and P4 are the relevant results because their count in the Measure2 column <= the fisure which appears in the card visual.  I cannot figure out how to filter the figures in the Total column by the figure which appears in the card visual.

Hope this helps.