Selecting unique shoppers in dataset

[Anonymous]

Hello Community,

I have a challenge I am hoping for some help to get started on

I have four relevant tables:

Fact Sales

Dim Category

Dim Product
Dim Users

DimCategory is structured as follows:
Category > Level 1 > Level 2 > - .. - > Level 5

I am looking to write a DAX measure where I can select a product (Dim Product) and then display a count of users who have bought the specific product but who has not bought any other products from the selected products category level.

So far I believe I need to use CALCULATETABLE() and then alter the filter context to exclude the users who fail the test but I am not sure how to structure it

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

>> I am looking to write a DAX measure where I can select a product (Dim Product) and then display a count of users who have bought the specific product but who has not bought any other products from the selected products category level.

could you give an example? based on the sample data below, if I select Category=A, Product=T1, then expected output= (?)

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Thank you for your reply, I have adapted your sample data a little to answer the confusion:

[amitchandak]

@Anonymous , a new measure , will give count of category where you got selected product not any other product

measure =

var _sel = allselected(product[Product])

var _cat = summarize(filter(allselected(Product), product[Product] in _sel) , Product[Category])

return

countx(filter(summarize(Fact, Product[Category], "_1", countrows(Fact), "_2", calculate(countrows(Fact), filter(Product, Product[Category] in _cat && not( product[Product] in _sel) ) ) ), not(isblank(_1)) && isblank(_2)),[Category])

[Anonymous]

Thank you very much for your great reply. I have tried building out the measure like you suggested but am getting an error:

VAR _selProd = ALLSELECTED( Product[Brand] )

VAR _Cat = SUMMARIZE(FILTER(ALLSELECTED('Product'), 'Product'[Brand] IN _selProd), 'Product'[CategoryId])

RETURN
    
    COUNTX(
        FILTER(
            SUMMARIZE(
                Purchase, Product[CategoryId],
                "_1", COUNTROWS( Purchase ), "_2",
                CALCULATE(
                    COUNTROWS(
                        Purchase
                    ), FILTER(
                        'Product',
                        'Product'[CategoryId] IN _Cat && NOT(Product[Brand] IN _selProd)                                               
                    )
                )
            ), NOT ( ISBLANK( "_1" ) )
                    && ISBLANK( "_2" )
                ), Product[CategoryId]
            )

 I changed the formula because I discovered an error. I am no longer getting an error but I am not getting any values

Edit: DAX formula changed because of an error