Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
JollyRoger01
Helper III
Helper III

How to replicate Excel COUNTIF formula?

I am struggling to get my head around the basic layout of DAX, even a simple COUNTIF formula. I have two tables in my data model connected by a field called System. I just wanted to calculate in this table how many rows are in Table 1 where the two row values for System are the same.

 

image.png

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@JollyRoger01 

I guess you have a one-to-many relationship from this table to table1. Add the following calculated column to the table on the one side

Count System = 
CALCULATE(
    COUNTA(Table1[System]),
    RELATEDTABLE(Table1)
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@JollyRoger01 

I guess you have a one-to-many relationship from this table to table1. Add the following calculated column to the table on the one side

Count System = 
CALCULATE(
    COUNTA(Table1[System]),
    RELATEDTABLE(Table1)
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you, that seems to work. Though I am a little confused as to what the function is actually doing. How does it know that I am trying to match the two columns? Is that implied from the one to many relationship already?

@JollyRoger01 

Yes, since you already have a valid relationship, CALCULATE activates it here. 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Okay, I understand that. But how would I do this if I didn't have a relationship?

 

@JollyRoger01 

You can do it this way:

Count System =
VAR __system = [System]
RETURN
    COUNTROWS ( FILTER ( ALL ( Table1[System] ), Table1[System] = __system ) )
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I can't seem to make that one work. Are you sure that is in the right format?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

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

Jan NL Carousel

Fabric Community Update - January 2025

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