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