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.
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:-
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.
Praisy Joy
Data Analyst
Addend Analytics
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
