Forum Discussion

Vallt's avatar
Vallt
Frequent Visitor
1 year ago
Solved

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...
  • xifeng_L's avatar
    xifeng_L
    1 year ago

    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's avatar
Vallt
Frequent Visitor
1 year ago

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's avatar
    xifeng_L
    Icon for Super User rankSuper User
    1 year ago

    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])
)