Forum Discussion

saumitra's avatar
saumitra
Helper I
5 years ago
Solved

Countif solution

Hi All, I am trying to run the Countif command in Power BI. I am using the following code for the same.    However, as I have mentioned in the picture, I want the query to give me an answer ...
Need Help
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi saumitra , 

    When "Ash Forbes" appears multiple times, two rows data are filtered out because the Date column in table is the same. You should add a column that can determine the order in which "Ash Fobes" appears. So I added an "index" column to the table, and then create the following calculated column.

    Countif = 
COUNTAX(
  FILTER(
    'Sheet1',
    'Sheet1'[Name] = EARLIER('Sheet1'[Name]) 
    && 'Sheet1'[Company] = EARLIER('Sheet1'[Company]) 
    && 'Sheet1'[index] <= EARLIER('Sheet1'[index])
  ),
  'Sheet1'[Name]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

Hi saumitra , 

When "Ash Forbes" appears multiple times, two rows data are filtered out because the Date column in table is the same. You should add a column that can determine the order in which "Ash Fobes" appears. So I added an "index" column to the table, and then create the following calculated column.

Countif = 
COUNTAX(
  FILTER(
    'Sheet1',
    'Sheet1'[Name] = EARLIER('Sheet1'[Name]) 
    && 'Sheet1'[Company] = EARLIER('Sheet1'[Company]) 
    && 'Sheet1'[index] <= EARLIER('Sheet1'[index])
  ),
  'Sheet1'[Name]
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

  • saumitra's avatar
    saumitra
    Helper I
    5 years ago

    v-kkf-msft Hey, thank you very much. I am getting the desired outcome now. However, just one query. In my example, the dates are same but suppose in future the dates are different, then what can be done? 

    Thanks again for solving the above problem. 🙂

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      5 years ago

      Hi saumitra ,

      If the same Name and Company appear on different dates, then the DAX you write in the first picture is right.

      Best Regards,

      Winniz