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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
scottm704
Helper I
Helper I

DAX COUNTIFS formula counting too many rows

I'm going to preface this with I am brand new at DAX and Power BI. I have found some examples of replicating Excel's COUNTIFS function in DAX but the issue I am running into is what I have written is returning too many values. Here is an example of the data I am using:
data.PNG

 

I need to count the number of times an ID is inspected during each year. The COUNTIF I plugged into Excel works as expected - COUNTIF(A:A, An, J:J, Jn) . I tried to replicate it in DAX with the following code:

 

 

 

 

 

2020Reads =

VAR __ID = Table[ID]

VAR __2020 = 2020

RETURN

COUNTROWS(

    FILTER(

      ALL(Table),

      Table[ID] = __ID &&

      Table[Year] = __2020

    )

)

 

 

 

 

 

When I put this in a matrix instead of ID 274520 showing a count of 1 it shows 3. Any advice on where I am going wrong would be appreciated.

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @scottm704 ,

 

Are you using it as a measure or as a calculated column?

 

If using as a calculated column you may try this:

Column = 
VAR _ID = 'Table'[Data]
VAR _Text = "A"
VAR _Count = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Data] = _ID
                && 'Table'[Text] = _Text)
    )

RETURN
_Count

 

CALCULATE helps in context trasition.

 

If you are using it as a measure, then you may use this:

 

Measure = 
VAR _Text = "A"
VAR _Count = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Text] = _Text)
    )

RETURN
_Count

 

I created it on a sample data where Text column is equivalent to Year in your case.

 

The you can put the ID in the row section of the matrix and measure above in the values.

 

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter 

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

Hello @scottm704 ,

 

Are you using it as a measure or as a calculated column?

 

If using as a calculated column you may try this:

Column = 
VAR _ID = 'Table'[Data]
VAR _Text = "A"
VAR _Count = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Data] = _ID
                && 'Table'[Text] = _Text)
    )

RETURN
_Count

 

CALCULATE helps in context trasition.

 

If you are using it as a measure, then you may use this:

 

Measure = 
VAR _Text = "A"
VAR _Count = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Text] = _Text)
    )

RETURN
_Count

 

I created it on a sample data where Text column is equivalent to Year in your case.

 

The you can put the ID in the row section of the matrix and measure above in the values.

 

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter 

@vivran22  Thanks for the reply. The calculated column you provided works - with a caveat. The column I created for 2020 works great, it accurately returns the number of times the ID was inspected in 2020.

 

When I try to create another column with the same formula but a different text criteria I am receiving a circular reference error. Is it because I am trying to do this on the same table? In my mind creating a column that counts ID and year shouldn't return a circular reference but maybe I am not understanding what is happening behind the scenes.

For anyone who may be wondering the issue I was running into is described here - https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

 

Once I specified ALLNOBLANKROWS(Table) in the FILTER context the dependency was removed and I was able to manipulate the data the way I needed. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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