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 

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

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