cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

COUNTIFS in Power BI

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

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

COUNTIFS in Excel are generally replaced by using CALCULATE.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

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
Not applicable

Hereis the example.

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

Thanks
Raj

Regular Visitor

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
Not applicable

🙂

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

Regular Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors