How to get count of a value from multiple columns?

Question

I have 5 columns in my table. All 5 of them may or may not have a certain value, say, "Completed". I want to get a count of the number of columns in a row of that table which contains this value.

For example, in one row there are 3 columns that contain the value , "Completed", so the answer in this case should be 3. In the next row of the table, there are only 2 columns that contain the value , "Completed", so in this case the answer should be 2.

I tried using CountAX but it didn't gave me the correct answer.

Thanks

alejandro zuleta · Accepted Answer · 2016-10-14 16:17:47Z

1

Create a calculated column using the following DAX expression:

CompletedCount =
VAR MyCount = 0
RETURN
    IF ( [Column1] = "Completed", MyCount + 1, MyCount )
        + IF ( [Column2] = "Completed", MyCount + 1, MyCount )
        + IF ( [Column3] = "Completed", MyCount + 1, MyCount )
        + IF ( [Column4] = "Completed", MyCount + 1, MyCount )
        + IF ( [Column5] = "Completed", MyCount + 1, MyCount )

In this case the calculated column will be named CompletedCount.

UPDATE: Variable usage is not required.

CompletedCount =
IF ( [Column1] = "Completed", 1, 0 )
    + IF ( [Column2] = "Completed", 1, 0 )
    + IF ( [Column3] = "Completed", 1, 0 )
    + IF ( [Column4] = "Completed", 1, 0 )
    + IF ( [Column5] = "Completed", 1, 0 )

Let me know if this helps.

edited Oct 14, 2016 at 16:17

answered Oct 14, 2016 at 15:27

alejandro zuleta

14.1k4 gold badges34 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get count of a value from multiple columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related