DAX for Determining Having a Product vs. ONLY having a Certain Product

[Blaggy]

I have dataset where I have been able to determin how many customers own a certain product.  My approach (with help from others on this board):

Product 1 Customer Count =

    CALCULATE(

        SUMX(

            VALUES(Table[Customer]),1

        ),Table,

        FILTER(Table,Table[Product] = "Product 1" && Table[Revenue] > 0)

    )

Product 2 Customer Count =

    CALCULATE(

        SUMX(

            VALUES(Table[Customer]),1

        ),Table,

        FILTER(Table,Table[Product] = "Product 2" && Table[Revenue] > 0)

    )

Product 3 Customer Count =

    CALCULATE(

        SUMX(

            VALUES(Table[Customer]),1

        ),Table,

        FILTER(Table,Table[Product] = "Product 3" && Table[Revenue] > 0)

    )

Count of Products Customers Own =

                [Product 1  Customer Count] + [Product 2 Customer Count] + [Product 3 Customer Count]

I am able to use these measures to provide me the data in the file  attached, and can produce the green highlighted table now.  

However, what I now want to be able to show is a counts of customers that own ONLY Product 1, ONLY Product 2 and ONLY Product 3 as well as other combinations, such as the number of customers that only own Product 1 and 3, etc.

What is the right DAX to do this?  I've tried several different approaches, but every one I've tried gets me to the same answer as the table in green.

Thanks!

[johnt75]

First create a new table of all the products which you can use in a slicer.

Owned Products = DISTINCT( 'Table'[Product] )

Do not connect this to any other tables.

You can now create a measure to show customers who only own the products chosen in the slicer

# customers own chosen products =
VAR NumChosenProducts =
    COUNTROWS ( 'Owned products' )
VAR SummaryTable =
    GROUPBY (
        CALCULATETABLE (
            SUMMARIZE ( 'Table', 'Table'[Customer], 'Table'[Product] ),
            TREATAS ( VALUES ( 'Owned products'[Product] ), 'Table'[Product] )
        ),
        'Table'[Customer],
        "@num rows", SUMX ( CURRENTGROUP (), 1 )
    )
VAR CustomersOwnAllChosenProducts =
    SELECTCOLUMNS (
        FILTER ( SummaryTable, [@num rows] = NumChosenProducts ),
        "Customer", 'Table'[Customer]
    )
VAR OtherProducts =
    EXCEPT ( ALL ( 'Table'[Product] ), VALUES ( 'Owned products'[Product] ) )
VAR CustomersOwnOtherProducts =
    CALCULATETABLE ( VALUES ( 'Table'[Customer] ), OtherProducts )
VAR CustomersOnlyChosenProducts =
    EXCEPT ( CustomersOwnAllChosenProducts, CustomersOwnOtherProducts )
VAR Result =
    COUNTROWS ( CustomersOnlyChosenProducts )
RETURN
    Result

[Blaggy]

Thank you, @johnt75 - appreciate the quick work on this!  

However, I don't know if a) I'm using this DAX in my PBI visuals correctly, and/or b) if I am, that it's giving me the intended result.

I have taken this new measure and put into a Matrix Visual with Fiscal Quarters in columns, the new "owned products[product]" field for the rows, and this new measure "# of customers own chosen products" as my values.  

First, what I cannot get from this is a view of the # of customers that only own certain combinations (say, Product 1 and Product 3) vs. only owning one of the products (say, just Product 1).

I assumed in this Matrix Visual, since I had the Products in the rows, that this would show the # of customers that only own that product, but the result appears to be too low, maybe because of the nuance above?

I may be misunderstanding how to use this new measure and your point about how to use this in a slicer, perhaps.

Thanks again.

[johnt75]

As long as you are using the column from Owned Products table in the matrix then I think the numbers should be correct.

As you say, in that visualization there would be no way to see who owned combinations of products. For that I think you would need to remove the products from the matrix, so you're just left with the measure and the financial quarter, and add a slicer on the Owned Products table, allowing you to choose multiple products to see how many people owned all of them.

[Blaggy]

Thank you, @johnt75 .  I'm trying to find some time to do a more detailed reconciliation between what this DAX is producing vs. a more manual Excel file I have to see if I can isolate why I'm getting different figures.  I understand what you're saying with the slicer - makes sense, but just not sure why the results are not as I was expecting.  Will revert once I can isolate differences and what might be causing that.