itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: calculate values for a specific object and show 0 instead of blank

This DAX formula will get the total of a specific value, in my example, I am asking to calculate the total for the second quarter of 2021

CALCULATE(SUM('table'[argument1]),'table'[argument2]="xxx")

power bi

In the other hand, if I want to calculate a specific criteria, I will use this formula:

CALCULATE(SUM('table'[argument1]),SEARCH("*XXX*",'table'[argument2],1,0))

power bi

NOTE: I put * but I will get the same result without (for instance "XXX")

Here, I tell it to sum only if it finds “@” in the “email” column for instance.

Below are simple formulas to look for a specific value:

  • IF(ISERROR(SEARCH("XXX",'table'[argument])),"not found","found")
  • IF(CONTAINSSTRING('table'[argument],"XXX"),"found","no found")

For CONTAINSSTRING, I can use it like that: CONTAINSSTRING('table'[argument],"XXX"). The result with be "TRUE" or "FALSE".

To see the result, first I will create a measure by clicking on “home -> new measure” and put the formula. Secondly, I will create a “card” in the visualization and in the “fields”, I put the “measure”. And in case if there is no result, instead to get a blank, with this one, I am asking to put zero:


var blank0=CALCULATE(COUNT('table'[argument]),'table'[argument]="xxx")
return
IF(ISBLANK(blank0),0,blank0)

NOTE: to count unique value, use “DISTINCTCOUNT” instead of “COUNT”.

The difference between “sum” and “count” is that for “sum”, the argument should be a number column (for instance 1, 52, etc.) and for “count”, a text column (for instance, inc001, inc002, etc.)

power bi
power bi power bi

And to count blank/empty cells and/or the contrary:

  • blank cell:
    • COUNTBLANK('table'[argument])
    • COUNTROWS(FILTER('table',ISBLANK('table'[argument])))
  • no empty cell:
    • COUNTA('table'[argument])
    • COUNTROWS(FILTER('table',NOT(ISBLANK('table'[argument]))))

NOTE: make sure that blank cells are “null” in the editor interface if not, formula to count cells not empty won´t return correct result (read Power BI: resolving the blank/empty cells that Power BI should not count them).

Interesting Topics