Forum Discussion
Solved
count specific text in a row
Dear Team,
I have a table
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 | Count of NotCompleted |
| Completed | NotCompleted | Completed | NotCompleted | NotCompleted | Completed | 3 |
| Completed | Completed | NotCompleted | Completed | Completed | Completed | 1 |
How to write a dax function to calculate the count of NotCompleted?
Thank You
HI asodhani
If you don't mind a bit of hardcoding this calculated column will work
Column = VAR S = "NotCompleted" RETURN IF('Table3'[Column 1]=S,1) + IF('Table3'[Column 2]=S,1) + IF('Table3'[Column 3]=S,1) + IF('Table3'[Column 4]=S,1) + IF('Table3'[Column 5]=S,1) + IF('Table3'[Column 6]=S,1)Otherwise, unpivot the data to rows and you can sum over a single column
Hi asodhani
Please give this a try.
Column = IF('Table3'[Column 1]="NotCompleted",1) + IF('Table3'[Column 2]="NotCompleted",1) + IF('Table3'[Column 3]="NotCompleted",1) + IF('Table3'[Column 4]="NotCompleted",1) + IF('Table3'[Column 5]="NotCompleted",1) + IF('Table3'[Column 6]="NotCompleted",1)
6 Replies
- Phil_Seamark
Microsoft Employee
HI asodhani
If you don't mind a bit of hardcoding this calculated column will work
Column = VAR S = "NotCompleted" RETURN IF('Table3'[Column 1]=S,1) + IF('Table3'[Column 2]=S,1) + IF('Table3'[Column 3]=S,1) + IF('Table3'[Column 4]=S,1) + IF('Table3'[Column 5]=S,1) + IF('Table3'[Column 6]=S,1)Otherwise, unpivot the data to rows and you can sum over a single column
How to unpivot data to rows? I have this table directly coming from a ERP Database
I can only do VAR.S but then it asks me for a column name. also I am unsure how to add the Return Formula after that.
Will be great if you can assist
- Phil_Seamark
