CountRows of Multiple Columns

[gthistlethwaite]

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

[Anonymous]

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]

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]

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]

The table is survey results with multiple columns.

[gthistlethwaite · Accepted solution]

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"))