locked
How to use Excel function Countif on DAX RRS feed

  • 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
    3 Food Box Caddy
    4 Food Waste Bin
    5 Green Box
    6 Green Box Net
    7 Wheeled Rubbish Bin

    Thank you in advance !


    erkindunya


    Wednesday, March 7, 2012 12:18 PM

All replies