Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

count calculation occurence

Hi,

  I have to find count for below data ...

 

Filter already applied is 

mesure = CALCULATE(DISTINCTCOUNT('Table'[policy_no]),FILTER('Table',('Table'[endorse value] > 0 )))
New changes i need to apply here are
  1. endorse value should zero or greater than zero 
  2. Concatenate the policy no + Endorse value and then apply the duplicate rule of counting it as “1” (one) if there are more than one of the same policy no + Endorse value.
  3. In this case with above data A1 policy no have 4 Endorse value and A1 policy no with endorse value 1,2 have duplicate...So with data total for A1, i have almost 6  ...but by avoiding duplicate with (Concatenate the policy no + Endorse value) count should be 4 for A1 alone....

How it possible in power bi?

 
Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi Anonymous 

    Based on your description, I create a sample, and it may give you pointers.

    -

    Create the measure:

    mesure2 = CALCULATE(DISTINCTCOUNT('Table'[Endorse value]),FILTER(ALLEXCEPT('Table','Table'[Policy_no]),'Table'[Endorse value]>=0))

    result:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

    mesure = CALCULATE(countx(summarize(FILTER('Table','Table'[endorse value] > 0 )'Table'[policy_no],[endorse value]), [policy_no]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      here getting count based on endorse value greater than 0.But i want endorse value =0....Is it possible?Here by using above measure its endorse value=0 is not considering..

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Anonymous 

    Based on your description, I create a sample, and it may give you pointers.

    -

    Create the measure:

    mesure2 = CALCULATE(DISTINCTCOUNT('Table'[Endorse value]),FILTER(ALLEXCEPT('Table','Table'[Policy_no]),'Table'[Endorse value]>=0))

    result:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

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