DAX - counting based on two columns

[BharatM]

Hi,

I am fairly new to DAX. I have been struggling all day to solve a particular problem and would be grateful if someone can help guide me. I have a list of customers and data looks like this (dummy example):

Customer ID	Choice
101	Desk
101	Chair
101	Chair
102	Chair
102	Chair
103	Desk
104	Desk
104	Chair
105	Chair
105	Chair
105	Desk
105	Chair
106	Desk

Eash customer can have multiple entries. However I want to extract only the ID of those customer who have chosen "Chair" only and nothing else - in this example 102 and 106. I have scratched my head and tried different thing and have failed. 

Thank you.

Bharat

[harakuruku]

Create a calculated column

IsChair = IF(Table1[Choice] = "Chair", 1, 0)

Then sum it in the display

[BharatM]

Will this not also count those customer IDs who have chair and desk or table? I want only those customers who have chair and nothing else as a selection.

[OwenAuger · Accepted solution]

@BharatM

I would suggest creating either a calculated column or measure using this code:

Chair only =
IF (
    CALCULATE (
        SELECTEDVALUE ( Choices[Choice] ),
        ALLEXCEPT ( Choices, Choices[Customer ID] )
    )
        = "Chair",
    1
)

Then apply a visual level filter (or any sort of filter in the case of a calculated column) Chair only = 1

[BharatM]

I can't thank you enough!!!!