Count of multiple columns into single column 

  • Post category:Power BI
  • Post author:
  • Post published:June 15, 2022

This is a small blog explaining the DAX expression to count multiple column values into a single column. 

Here we have a sample table Table1, which has columns named ID, Dim1, Dim2, Dim3 and Dim4.  

Table

Description automatically generated with medium confidence

The columns Dim1 to Dim4 has the values “Yes” or “No”. 

Here, I need to count total “Yes” from these four columns and add it in a new column. For eg, first row has 3 Yes, second has 4 Yes etc. 

I created a calculated column to count total Yes in each row from these 4 different columns and it is resulted as:-

A screenshot of a computer

Description automatically generated with low confidence

Here I’m giving you one simple DAX expression which creates a calculated column.  

Count Yes = 

IF ( Table1[Dim1] = “Yes”, 1, 0 ) 

    + IF ( Table1[Dim2] = “Yes”, 1, 0 ) 

    + IF ( Table1[Dim3] = “Yes”, 1, 0 ) 

    + IF ( Table1[Dim4] = “Yes”, 1, 0 ) 

Another solution is that creating a measure which results the same. 

Count = 

SUMX ( 

    UNION ( 

        SELECTCOLUMNS ( ‘Table1’, “ContainsYes”, ‘Table1′[Dim1] ), 

        SELECTCOLUMNS ( ‘Table1’, “ContainsYes”, ‘Table1′[Dim2] ), 

        SELECTCOLUMNS ( ‘Table1’, “Containsyes”, ‘Table1′[Dim3] ), 

        SELECTCOLUMNS ( ‘Table1’, “ContainsYes”, ‘Table1′[Dim4] ) 

    ), 

    IF ( [ContainsYes] = “Yes”, 1, 0 ) 

The measure “Count” and result is  given below. 

Table

Description automatically generated with low confidence

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com. 

Praisy Joy 

Data Analyst 

Addend Analytics