Forum Discussion

dgdgdg122db's avatar
dgdgdg122db
Helper 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 I...
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.

v-yingjl's avatar
v-yingjl
Community 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.