Forum Discussion

RMW13's avatar
RMW13
Regular Visitor
3 years ago
Solved

Counting Yes, No, N/A for Checklist Entries

Hello Everyone,

 

I'm looking to use counts to calculate a score on each checklist submission that is processed by our quality checking team.  This data is collected by a smartsheet form and each entry creates a new row in the smartsheet grid, with the questions in the checklist being the columns of the grid.

 

So far, I have made this work on a small scale by referencing the link below, which is over 3 years old now.  

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Count-columns-with-Yes/m-p/727533

 

And using the DAX below,

 

Numb. Correct =
VAR myrow={[Correct Template Used?],[Reference Point Correct?],[Correct Colours Used?]}
RETURN
COUNTROWS(Filter(myrow,[Value]=1))
 
The above works, but our checklist has almost 50 questions, and it is a growing list, so I am concerned that it will become very difficult to manage as more questions (e.g. columns) are added.  I'm curious is there a way I can get the same result without having to label each of the columns in the DAX so that if a new column is added the column will account for it.
 
Any thoughts?
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi RMW13 ,

     

    Please try the follow steps:

    1. Add a index column in Power Query Editor;

    2. Select the index column and unpivot other columns;

    3. Please new a measure like:

    Measure = CALCULATE( COUNTROWS('Table'), 'Table'[Value] = 1)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi RMW13 ,

     

    Please try the follow steps:

    1. Add a index column in Power Query Editor;

    2. Select the index column and unpivot other columns;

    3. Please new a measure like:

    Measure = CALCULATE( COUNTROWS('Table'), 'Table'[Value] = 1)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum