Forum Discussion
DAX distinctcount with a condition
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?
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~
6 Replies
Vallt , Create a measure using
Number of distinct customers for this item =
CALCULATE(
DISTINCTCOUNT('YourTable'[Customer number]),
ALLEXCEPT('YourTable', 'YourTable'[Item number])
)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~
Thank you! Works just like I expected!
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?
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]) )
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.
