Forum Discussion

EmmaX's avatar
EmmaX
Frequent Visitor
8 years ago
Solved

count specific value in multiple columns

Hello everyone,   I'm very new to Power BI so please help me on this.   Say I have a table that looks something like this:     and I want to count the number of "yes" in each column, and...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi EmmaX

     

    You could create a calcuated table that summarizes your raw data

     

    Table = SUMMARIZECOLUMNS(
                    'Table1'[Brand],
                    "Comfort",CALCULATE(COUNTROWS('Table1'),'Table1'[comfort]="Yes"),
                    "Affordable Price",CALCULATE(COUNTROWS('Table1'),'Table1'[affordable price]="Yes"),
                    "Ease to Clean",CALCULATE(COUNTROWS('Table1'),'Table1'[ease to clean]="Yes")
                    )

     

    This produces the following output based on your sample data.

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi EmmaX

 

You could create a calcuated table that summarizes your raw data

 

Table = SUMMARIZECOLUMNS(
                    'Table1'[Brand],
                    "Comfort",CALCULATE(COUNTROWS('Table1'),'Table1'[comfort]="Yes"),
                    "Affordable Price",CALCULATE(COUNTROWS('Table1'),'Table1'[affordable price]="Yes"),
                    "Ease to Clean",CALCULATE(COUNTROWS('Table1'),'Table1'[ease to clean]="Yes")
                    )

 

This produces the following output based on your sample data.

 

 

 

  • EmmaX's avatar
    EmmaX
    Frequent Visitor
    8 years ago

    Wow, thanks for the quick reply.

     

    So it looks like in BI you have to manually code each column...which would be an issue when you have many columns...