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
Gaurav Lakhotia
Gaurav holds an MBA in Business Analytics. As a data geek and avid learner, he has earned accolades including Microsoft Certified Data Analyst, Microsoft Certified Azure Administrator, and active Power BI Community contributor. He is also a Microsoft Certified Trainer. With critical thinking and project delivery skills, he strives to achieve customer delight in Data Analytics projects. Gaurav loves travelling with friends when he is not busy working on data.
