Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors