CODEWITHSUNDEEP
powerbidax
Himanshu Malik
Himanshu Malik

Reputation: 35

How to get count of a value from multiple columns?

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

Upvotes: 1

Views: 2084

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

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.

Upvotes: 1

Related Questions