Count specific valuen from different columns in each row

[Luiggo07]

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!

[amitchandak · Accepted solution]

@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.

[Luiggo07]

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!

[Luiggo07]

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:

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

57

Miguel

2/28/2022

51201689

CRO

Yes

Not Applicable

Yes

No

Not Applicable

No

Yes

Yes

58

Miguel

2/28/2022

50790008

CRO

No

Yes

Yes

Yes

Not Applicable

No

Yes

Yes

59

Diana

2/28/2022

50690381

CRO

Yes

Yes

No

No

Not Applicable

Yes

Yes

No

60

Diana

2/28/2022

50045242

CRO

Yes

Not Applicable

Yes

Yes

Yes

No

Yes

Yes

61

Juana

2/28/2022

51103464

SOL

Not Applicable

Yes

Yes

No

Yes

Yes

No

No

62

Juana

2/28/2022

50454420

SOL

Yes

Yes

Not Applicable

No

Yes

Yes

Yes

Yes

63

Juana

2/28/2022

50299770

PRIM

Yes

No

Not Applicable

Yes

Yes

Yes

No

Not Applicable

64

Arturo

2/28/2022

288984

PRIM

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

65

Diego

2/28/2022

288677

SEC

Yes

Not Applicable

Yes

Yes

Yes

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.