itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: count occurrences

To count occurrences, I just need to include the FILTER and EARLIER functions with this formula:

FILTER('table',EARLIER('table'[argument1])='table'[argument1])

With another function. And if I need to calculate it based on criteria, I need to add another argument for each condition like that:

FILTER('table',EARLIER('table'[argument2])='table'[argument2] && EARLIER('table'[argument1])='table'[argument1])

For instance, I have this table:

power bi

For “column” I use this formula to count the number of occurrences for “support”:

COUNTX(first formula,'table'[argument1])

power bi

For “column 2”, calculating it for each “number”:

  • COUNTX(second formula,'table'[argument1])
    power bi
  • Or COUNTX(first formula but by using [argument2],'table'[argument1])
    power bi

NOTE: in spite that the first option works for my example, based on the data I have, the second option works better

In both examples, I use the COUNTX function but I can get the same results by using the CALCULATE and COUNT functions:

CALCULATE(COUNT('table'[argument2]),first formula or second formula)

Interesting Topics