Forum Discussion

dgdgdg122db's avatar
dgdgdg122db
Icon for Helper II rankHelper II
6 years ago
Solved

calculate occurrence based on condition

Hi I have a table where the document ID and customer ID can be duplicates. I want to know the customer's occurrence based on the year without creating another table (removing duplicates on Document ID).

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi dgdgdg122db ,

     

     

    Use this measure.

     

    Measure = CALCULATE (DISTINCTCOUNT('TABLE'[Document Id]), ALLEXCEPT('Table','Table'[CustomerId]))

     

    Regards,

    Harsh Nathani

  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi dgdgdg122db ,

    You can create a calculated column like this which is simliar as @ harshnathani mentioned:

    Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Document ID] ),
    ALLEXCEPT ( 'Table', 'Table'[CustomerID], 'Table'[Year] )
)

     

    By the way, I think the year of the row 2019/1/14 should be 2019 not 2020 to get the refult😀

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi dgdgdg122db ,

     

     

    Use this measure.

     

    Measure = CALCULATE (DISTINCTCOUNT('TABLE'[Document Id]), ALLEXCEPT('Table','Table'[CustomerId]))

     

    Regards,

    Harsh Nathani

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi dgdgdg122db ,

    You can create a calculated column like this which is simliar as @ harshnathani mentioned:

    Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Document ID] ),
    ALLEXCEPT ( 'Table', 'Table'[CustomerID], 'Table'[Year] )
)

     

    By the way, I think the year of the row 2019/1/14 should be 2019 not 2020 to get the refult😀

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.