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

[NataliG]

Hi,

I've gone through many posts on this topic and unfortunately nothing seems to work. Maybe my knowledge of DAX is limited and i'm not able to customise the solutions as per my needs so eager to receive any help.

I'm trying to identify duplicate booking of purchase invoices in my dataset which is something like the table below. I need to be able to use a countifs like functionality to check for rows where invoice no# and transaction amount are the same.  so in excel i would have added a column after D with the following formula :COUNTIFS(C:C,C2,D:D,D2). And then i could proceed to evaluate any rows that returned a value greater than 1. I have not been able to achieve this yet in power bi. any help is greatly appreciated.

A	B	C	D
Vendor ID.#	Name	Invoice No.#	Transaction Amount
600754	aaaa	123	366
989111	bbbb	234	320
242001	cccc	86030	242
600754	aaaa	123	366

[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