cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: CountRows of Multiple Columns

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

View solution in original post

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

Re: CountRows of Multiple Columns

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?

Highlighted
Frequent Visitor

Re: CountRows of Multiple Columns

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

View solution in original post

Highlighted
Frequent Visitor

Re: CountRows of Multiple Columns

The table is survey results with multiple columns.

Highlighted
Anonymous
Not applicable

Re: CountRows of Multiple Columns

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.

Highlighted
Frequent Visitor

Re: CountRows of Multiple Columns

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors