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

    • JollyRoger01's avatar
      JollyRoger01
      Helper III
      5 years ago

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

       

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        JollyRoger01 

        You can do it this way:

        Count System =
VAR __system = [System]
RETURN
    COUNTROWS ( FILTER ( ALL ( Table1[System] ), Table1[System] = __system ) )