Forum Discussion

JollyRoger01's avatar
JollyRoger01
Helper III
5 years ago
Solved

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.

 

  • Fowmy's avatar
    Fowmy
    5 years ago

    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)
)

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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)
)
    • JollyRoger01's avatar
      JollyRoger01
      Helper III
      5 years ago

      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?

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        JollyRoger01 

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