calculate occurrence based on condition

[dgdgdg122db]

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

[harshnathani · Accepted solution]

Hi @dgdgdg122db ,

Use this measure.

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

Regards,

Harsh Nathani

[v-yingjl · Accepted solution]

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.

[harshnathani · Accepted solution]

Hi @dgdgdg122db ,

Use this measure.

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

Regards,

Harsh Nathani

[amitchandak]

@dgdgdg122db ,

Try like

countx(summarize(table,table[document ID], table[customerID]),[CustomerID])