Forum Discussion

asodhani's avatar
asodhani
Frequent Visitor
8 years ago
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 ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi asodhani

 

Are you creating it as a calculated column in a Power BI table?

asodhani's avatar
asodhani
Frequent Visitor
8 years ago

Phil_Seamark

 

Yes that is correct.

 

Unable to find the best option

 

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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)