Forum Discussion

gthistlethwaite's avatar
gthistlethwaite
Frequent Visitor
8 years ago
Solved

CountRows of Multiple Columns

I have 4 columns of data.

I need to sum the 4 columns based on a text value.

 

I was able to perform on 1 column, but then receive an error trying to add these together.

 

AsiaTTL = COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat1]="Asia")+ COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat2]="Asia")+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat3]="Asia")+ COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat4]="Asia")))))

 

Is this the correct DAX expression?

 

Many thanks,

Gary

  • gthistlethwaite's avatar
    gthistlethwaite
    8 years ago

    Fixed now, it required an extra closing bracket before the next +COUNTROWS:

     

    AsiaTTL = COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat1]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat2]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat3]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat4]="Asi"))

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Looks like you have 4 columns that are all award based, but are different categories.

     

    I would UNPIVOT the data in Query Editor.  Highlight all 4 columns, then right click one of the column headers.  Click "Unpivot Columns".

     

    You'll end up with 2 columns:  One will be the 4 different Categories (Cat1, Cat2, Cat3, Cat4), and the other will be the country ("Asia", etc).

     

    Now your DAX measure will be this:

     

    [# Awards] = 
CALCULATE(
    COUNTROWS('QHSE Award Voting'),
    Table[Value] = "Asia"
)

    If you rename the Attribute and Value columns that Query Editor automatically gives (which I recommend), be sure and update the formula above.

    • gthistlethwaite's avatar
      gthistlethwaite
      Frequent Visitor
      8 years ago

      Thanks Anonymous,

       

      I will try that for future reference, just not a nice original dataset to work with I'm afraid, the source is a SharePoint List.#

       

      Cheers,

      Gary

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I'm not sure why you are using a filter here, wouldn't countrows(tablename,cat1="Asia") work for each section of your formula?

     

    Also, are you wanting it so that if Asia's entered in more than one column for a given row it counts more than once?

    • gthistlethwaite's avatar
      gthistlethwaite
      Frequent Visitor
      8 years ago

      Fixed now, it required an extra closing bracket before the next +COUNTROWS:

       

      AsiaTTL = COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat1]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat2]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat3]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat4]="Asi"))