Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
asodhani
Frequent Visitor

count specific text in a row

Dear Team,

 

I have a table

 

Column 1Column 2Column 3Column 4Column 5Column 6Count of NotCompleted
CompletedNotCompletedCompletedNotCompletedNotCompletedCompleted3
CompletedCompletedNotCompletedCompletedCompletedCompleted1

 

How to write a dax function to calculate the count of NotCompleted?

 

Thank You

2 ACCEPTED SOLUTIONS
Phil_Seamark
Microsoft Employee
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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

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) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft Employee
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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

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

Hi @asodhani

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Yes that is correct.

 

Unable to find the best option

 

 var.s.pngvar.s2.png

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) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

How to unpivot data to rows? I have this table directly coming from a ERP Database

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.