COUNTIFS in Power BI

[Dan27]

Hello all,

Hoping someone can help. I'm trying to recreate the below excel formula in Power BI

=COUNTIFS(G:G,"<="&H:H,AL:AL,"")

I've tried combinations of 

Count = COUNTROWS(FILTER( 

I've referred to my field names rather than the column letter etc but still can't get it to work. How can I do he formula above but of course subsitute column names for the fields.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @Dan27,

Based on this sample data, is 3 the desired result? If so, please new a measure and add it to card visual.

Measure1 = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[G]<=Table2[H]))

For more advice, please illustrate your requirement with actual sample data and desired result rather than just the Excel functions.

Regards,

Yuliana Gu

[Greg_Deckler]

COUNTIFS in Excel are generally replaced by using CALCULATE.

[Dan27]

Thanks Greg but in what format?

If I use FieldName = CALCULATE([Field1] <= [Field2], " ") for example then I get a message saying the maximum argument count is 2. 

Appreciated. Thanks.

[Anonymous]

Hereis the example.

CALCULATE ( COUNT(Table1[Column1]), FILTER ( Table1, Table1[column1] < Table1[Clumn2]))

Thanks
Raj

[Dan27]

Thanks Raj

What you've put is really helpful but it still won't work.

I 'm trying to work out where if Field or column 'G' is less than or equal to column 'H' then return the values of column 'AL' else return a " " blank.

I've tried a few variations of what you suggested but none work. Where above I've referenced column letters I have replaced these with (Table[field]) accordingly.

Thanks

Dan

[Anonymous]

🙂

That was the example of CALCULATE.

Here is teh syntax for your Question:

Calculated_Column=IF(Table1[ColumnG] <= Table1[ColumnH] , Table1[ColumnAL], BLANK()

If you want count.

Cnt_Measure = COUNT(Table1[Calculated_Column])

Thanks

Raj

[Dan27]

Hi Raj

Thanks again for your help but again this isn't getting me the desired output because it's not performinng the count part.

I'm wanting to return a count of occurrences within a range. So my Excel equivalent would be

=COUNTIFS(G:G,"<="&H:H,AL:AL,"")

In Power BI this function doesn't work. I'm unsure if I can nest a COUNT and IF formula but so far what I've tried hasn't worked.

Thanks

Dan