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
Luiggo07
Frequent Visitor

Count specific valuen from different columns in each row

Hi, 

 

I'm working on a dashboard for an audit process. Daily I receive about 60 or 70 new entries from a form, each one is a new row. This audits have different types. I have 200 questions (colums) in total and this questions response may be "Yes", "No", "Not Applicable" and if the audit is from a different type, the response is blank.

 

What I need is to count the responses with "Yes" & "No" to make some calculations for each row.

 

I've been searching for a solutions but I've had no luck...

 

Thanks!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Luiggo07 , One option, I can think of, is to unpivot the question and bring them into rows

 

https://radacad.com/pivot-and-unpivot-with-power-bi

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Luiggo07 , One option, I can think of, is to unpivot the question and bring them into rows

 

https://radacad.com/pivot-and-unpivot-with-power-bi

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

I find out that the error was in my formula, not in the process. 

I get the result I wanted with the unpivot process and the CALCULATE / COUNTROWS formula. To avoid having an error with the different format ( text/number/dates/etc. values) i just duplicate the Value column and use some filters in the power query. Thanks for your help!

 

Thanks for your quick response, amitchandak. I tried with the option to unpivot the questions to birnd them into rows but I get the same number for all audits:

Luiggo07_0-1662045708080.png

 

Here is an example of mi table:

ID1NameDateCase IDTypeQuestion 1Question 2Question 3Question 4Question 5Question 6Question 7Question 8Question 9Question 10Question 11Question 12Question 13Question 14Question 15Question 16Question 17Question 18Question 19Question 20

57Miguel2/28/202251201689CROYesNot ApplicableYes   No    Not ApplicableNo  Yes Yes  
58Miguel2/28/202250790008CRONoYesYes   Yes    Not ApplicableNo  Yes Yes  
59Diana2/28/202250690381CROYesYesNo   No    Not ApplicableYes  Yes No  
60Diana2/28/202250045242CROYesNot ApplicableYes   Yes    YesNo  Yes Yes  
61Juana2/28/202251103464SOL Not ApplicableYesYesNo  YesYes   NoNo     
62Juana2/28/202250454420SOL Yes YesNot ApplicableNo  YesYes   YesYes     
63Juana2/28/202250299770PRIMYes No        Not ApplicableYes   YesYesNoNot Applicable
64Arturo2/28/2022288984PRIMYes Yes        YesYes   YesYesYesNo
65Diego2/28/2022288677SEC Yes  Not ApplicableYesYesYes Yes     Not Applicable  Yes

 

I used Power Automa to send the responses to an excel, and in the excel i can easily have the formula:
=COUNTIF(Table1[@[Question 1]:[Question 20]],"Yes")
=COUNTIF(Table1[@[Question 1]:[Question 20]],"No")
But the formula does not update when a new entry is registered by Power Automate and I can't do this manually everytime.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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