COUNTIF function in power bi

[SravaniBudde]

Hi ,

I am trying to convert excel fromulas inot power bi. Can u please help in convert this.

1.COUNTIFS([@[REJECTED_DATE]],">0")

2.COUNTIFS(Table1[@[ESCAL_TO1]:[ESCAL_TO3_DT]],">0")

[SravaniBudde]

can u tell for the below excel formula COUNTIFS(Table1[@[ESCAL_TO1]:[ESCAL_TO3_DT]],">0") to convert to power bi

if datatypes are different

ESCAL_TO1 is text with values ''adbcds'

and ESCAL_TO3_DT is date with value "10/31/2022"

i am getting below error 

[SravaniBudde]

Hi, Second formula actually 

ESCAL_TO1 is text with values ''adbcds'

and ESCAL_TO3_DT is date with value "10/31/2022"

how do i convert the COUNTIFS(Table1[@[ESCAL_TO1]:[ESCAL_TO3_DT]],">0") excel formula to power bi in this case.

[SravaniBudde]

Hi ,

Thanks for the reply.

But for the first formula mentioned if i use it am getting wrong value for my data set . May i know what could be reason.

[PhilipTreacy]

Hi @SravaniBudde 

I don't know.  I'd need to see your file to see what is going on.  Can you provide it please.

regards

Phil

[SravaniBudde]

It is resolved isntead i created custom column instead measure column. it resolved my issue now. how can i keep blank values count to 0

[PhilipTreacy]

Hi @SravaniBudde 

Download example PBIX file

For the 1st one you can use

COUNTIF Rejected Date = CALCULATE(COUNTROWS('DataTable'), FILTER('DataTable', NOT(ISBLANK('DataTable'[Rejected_Date]))))

For the 2nd one use something like this.  You'll need to list the columns in your table separately in the formula.  My dummy table has 3 columns that need to be checked.

COUNTIFS = CALCULATE(COUNTROWS('DataTable'), FILTER('DataTable', NOT(ISBLANK('DataTable'[Escal_To1])) || NOT(ISBLANK('DataTable'[Escal_To2])) || NOT(ISBLANK('DataTable'[Escal_To3_DT]))))

You can test the date either by using e.g. NOT(ISBLANK('DataTable'[Rejected_Date])) or 'DataTable'[Rejected_Date] > 0

Regards

Phil