DAX distinctcount with a condition

[Vallt]

Hello,

I am new to DAX so I need help on this. To describe my problem simply I have a data table with first row "Item number", second row "customer number". For each sale of item XXXXX there is a new row with a different or sometimes the same customer number. I want to add a calculated column to the table which counts the distinct number of customers for each item nuber. It should look like this:

Item number    Customer number       Number of distinct customers for this item

99                             11                                3

82                             22                                1

55                             34                                1

99                             55                                3

99                             11                                3

99                             99                                3

In Excel I used countif to count the customer numbers  with condition the item number. =Countif(column'Customer numer';B2). This way Excel counts the number of customers for each item, but I could not manage to get their distinct count. In the end I used a pivot for this distinct count. How can I manage to get the count of distinct customers for each item in DAX?

[Vallt]

Hello, I made a visual table with measures for the sales and customers counts and pcs sold for each item per sales center. I created an automatic calendar and added a slicer for the time period to the visual. Now when I change the time period the data is changing dynamically. So far everything is fine and I thank you all for your help! I need to do one more thing - I must create an option for excluding customers from the measures that count number of customers, number of sales and sold pcs for each item. Can I create a slicer in the visual that shows all the customers and choose which to exclude (similat to a pivot table)? Can this slicer use the search option as the customers are several thousand? I need to do this but I am not sure which is the best way.

[Vallt]

Hello again, what if there are two conditions? I want to count the distinct customers for each item but only for sales from sales center 1?

Item     Sales center     Customer number     Distinct customer per item for sales center 1

99             1                        33                                   2

99             3                        45                                   2

99             1                        41                                   2

88             2                        40                                   0

99             1                        33                                   2

Also if I do this as a measure instead of calculated column and add it to a table visual, will i be able to use slicer to change the time period?

[xifeng_L]

The following measures may be able to achieve your needs:

Result = 
CALCULATE(
    DISTINCTCOUNT('Table'[Customer number]),
    ALLEXCEPT('Table','Table'[Item number]),
    'Table'[Sales Center]=1,
    VALUES('Calendar'[Date])
)

[xifeng_L · Accepted solution]

Can try the below calculated column expression:

Result = CALCULATE(DISTINCTCOUNT('Table'[Customer number]),ALLEXCEPT('Table','Table'[Item number]))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Vallt]

Thank you! Works just like I expected!

[bhanu_gautam]

@Vallt , Create a measure using

Number of distinct customers for this item =
CALCULATE(
DISTINCTCOUNT('YourTable'[Customer number]),
ALLEXCEPT('YourTable', 'YourTable'[Item number])
)