Forum Discussion

kheira's avatar
kheira
Frequent Visitor
5 years ago
Solved

count without duplicates

hi 
i have two columns and i want to count how many product id in statmeent without duplicates production with the same id 
how i can do that with dax? 

 

  • Anonymous's avatar
    Anonymous
    5 years ago
    Hello kheira 
    Count I want =
    VAR StatementCount =
    CALCULATE(
    DISTINCTCOUNT('Table'[Satement ID])
    )
    VAR ProductCount =
    CALCULATE(
    COUNT('Table'[Product ID]),
    ALL('Table'[Product ID])
    )
    RETURN
    ProductCount - StatementCount

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Hello kheira 
    Count I want =
    VAR StatementCount =
    CALCULATE(
    DISTINCTCOUNT('Table'[Satement ID])
    )
    VAR ProductCount =
    CALCULATE(
    COUNT('Table'[Product ID]),
    ALL('Table'[Product ID])
    )
    RETURN
    ProductCount - StatementCount

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi kheira 

    1. Place Statement ID in a table visual

    2. Place this measure in the visual

    Measure =
DISTINCTCOUNT( Table1[Product ID])

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • kheira's avatar
      kheira
      Frequent Visitor
      5 years ago

      thank u 
      but i want to add new coulmn dax to get the result .. i tried to do dax :

      CALCULATE( COUNT('Table'[product id]), ALLEXCEPT('Table','Table'[product id])) - CALCULATE( DISTINCTCOUNT('Table'[statment id]), ALLEXCEPT('Table','Table'[statment id]))
      but i want  the count  is 4 in "statment id "=6

       

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    kheira 

    I guess you mean a 3 for the statement id 6

    New column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Product ID] ),
    ALLEXCEPT ( Table1, Table1[statement id] )
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers