Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"))
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.
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
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?
The table is survey results with 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"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |