How to count rows based on two conditions simultaneously in Power BI

[Anonymous]

Hi. anyone can help?

Currently, I am assisting my colleague with a Power BI dashboard for employee activities. Our user requirement is that we want to calculate how many employees have achieved the KPI where they must meet these 2 conditions:

1. The total activities that they joined must be >= 12

2. Among the activities that they joined, they MUST have at least 1x joined Health Screening activity
   
   

Hence, to follow these 2 conditions I have created a 'measure' in Power BI with IF conditions as below:

IF(COUNT('COHS Activities'[Id]) >= 12 && CONTAINS('COHS Activities','COHS Activities'[Pillar], "Health Screening / Pemeriksaan Kesihatan"), <ResultIfTrue>, <Blank> )))

Hence, from the IF condition above, for the <ResultIfTrue> part, I want to ask if there could be any DAX command/features that can COUNT rows based on TWO conditions simultaneously?

Initially, we highlighted the row that met these 2 conditions in green. Hence in short, our user requirement now is to count how many green rows are there in the table. Please refer below image:

[v-jayw-msft]

Hi @Anonymous ,

Does the above reply solve the problem?

If it works, please consider to mark it as a solution.

Best Regards,

Jay

[Anonymous · Accepted solution]

Suppose you have a result table with each conditons.

[Anonymous]

Hi @Anonymous ,

I'm pretty sure your solution should be working. However, since I don't have specific table for the activities listed by columns, hence I could not do as per you suggested. I only have a table which have only 1column that specifies all the activities as shown below:

so from this, i use matrix visual on the report, hence it shows all the activities separately by columns.

Could you please help more on this? Thank you so much in advance.

[Anonymous]

Only 1 column?  if there should be another column. if not, how do you group it ?

[tamerj1]

Hi @Anonymous 
Please try

Count =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'COHS Activities'[Employee Id] ),
            "@Check1",
                CALCULATE (
                    COUNT ( 'COHS Activities'[Id] ),
                    'COHS Activities'[Pillar] = "Health Screening / Pemeriksaan Kesihatan"
                ),
            "@Check2", CALCULATE ( COUNT ( 'COHS Activities'[Id] ) )
        ),
        [@Check1] > 0 && [@Check2] >= 12
    )
)

[Anonymous]

Hi @tamerj1,

Thank you so much for your help. I've tried to do as you suggested. It seems like there are no errors but its number is not shown on my card visual. You can refer to the image below.

Do you think you can help with this? I am not sure what is the problem. TQ in advance!