COUNTIF IN DAX with filters

[carrollag]

I am new to PowerBI and am trying to figure out how to convert the below formula into DAX. This formula is being used to filter and pull a total count from multiple columns located within one very large report.  

=SUM(COUNTIFS('REG Major Reports Combined'!G:G, "Y", 'REG Major Reports Combined'!H:H, "MAT", 'REG Major Reports Combined'!I:I, "MEC"), COUNTIFS('REG Major Reports Combined'!G:G, "Y", 'REG Major Reports Combined'!Q:Q, "MAT", 'REG Major Reports Combined'!R:R, "MEC"))

Thanks! 

[v-yalanwu-msft · Accepted solution]

Hi, @carrollag ;

You could create a measure as follows:

count =
COUNTROWS (
    FILTER ( 'REG Major Reports Combined', [G] = "Y" && [H] = "MAT" && [I] = "MEC" )
)
    + COUNTROWS (
        FILTER ( 'REG Major Reports Combined', [G] = "Y" && [Q] = "MAT" && [R] = "MEC" )
    )

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

So you want a sum of countifs? Here is one way to achieve this:

Example data:

Dax:

Countifs = COUNTROWS(FILTER('Matrix example','Matrix example'[Material]="Wood"&&'Matrix example'[Item]="Item1"))

So here we count the rows where material is Wood and item is Item1


End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[amitchandak]

@carrollag , You can have measure like

countrows(filter('REG Major Reports Combined'[G] = "Y" && 'REG Major Reports Combined'[H] = "MAT" && 'REG Major Reports Combined'[I] = "MAT" ))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.