# How to use Excel function Countif on DAX

• ### General discussion

• Hi All,

I got a problem creating a report with PowerPivot, I got a table has 7 columns holds duplicated values, the values is "string" in Excel you can use "COUNTIF" to calculate how many value 1, value 2 .. etc on on worksheet =COUNTIF(A2:G8,"Value 1")

My problems how can I convert that "COUNTIF" to Dax function?

***************** Table *********************************

 Data 1 Data 2 Data 3 Data 4 Data 5 Data 6 Data 7 Value 1 Value 2 Value 1 Value 7 Value 7 Value 7 Value 3 Value 2 Value 2 Value 3 Value 6 Value 1 Value 7 Value 3 Value 3 Value 5 Value 3 Value 6 Value 2 Value 3 Value 7 Value 4 Value 2 Value 1 Value 1 Value 3 Value 1 Value 7 Value 5 Value 5 Value 5 Value 1 Value 6 Value 2 Value 5 Value 6 Value 5 Value 6 Value 1 Value 6 Value 1 Value 7 Value 7 Value 2 Value 5 Value 5 Value 3 Value 4 Value 1

I can get correct totals using below DAX FORMULA but I need find totals for each individual values in the 7 columns how can I do that in DAX?

DAX FORMULA to calculate totals in 7 columns I also created :

=SUMX (
DataLookups,
SUMX (
Goods,
IF (Goods[Data 1] = DataLookups[Data Type], 1, 0) +
IF (Goods[Data 2] = DataLookups[Data Type], 1, 0) +
IF (Goods[Data 3] = DataLookups[Data Type], 1, 0) +
IF (Goods[Data 4] = DataLookups[Data Type], 1, 0) +
IF (Goods[Data 5] = DataLookups[Data Type], 1, 0) +
IF (Goods[Data 6] = DataLookups[Data Type], 1, 0) +                                                                                                                               IF (Goods[Data 7] = DataLookups[Data Type], 1, 0)

)
)

Look up table:

ID      DataLookups
1 Black Box Lid
2 Black Recycle Box
4 Food Waste Bin
5 Green Box
6 Green Box Net
7 Wheeled Rubbish Bin