Card to show an equivalent of excel countif

[Anonymous]

I have a card that i want to show the number of projects that where ProjectStatus = Active, and StatusRAG = Red.
I was using a filter on the card and that worked until I have started using bookmarks. When resetting the filters in the service, the filter on the cards are removed resulting in an incorrect value being shown.
I assume there is a more robust method for setting these values, can anybody suggest what is best?

PS, The card is using the following formula so that it will show 0 insted of (Blank)

zzMeasureProjectStatusActiveIF =
IF (
COUNTAX('Projects (2)', 'Projects (2)'[ProjectStatus] = "Active") = BLANK (),
"0",
COUNTAX('Projects (2)', 'Projects (2)'[ProjectStatus] = "Active")
)

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[vivran22]

Hi,

Has this been resolved?

Rgds,
Vivek

[vivran22 · Accepted solution]

@Anonymous 

You may also use:

CountA Filter =
COUNTAX (
    'Projects (2),
    IF (
        'Projects (2)'[ProjectStatus] = "Active"
            && 'Projects (2)'[StatusRAG ] = "Red",
        'Projects (2)'[ProjectStatus]
    )
)

Rgds,

Vivek

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MattAllington]

The better approach is to use calculate. 

Something like this (substitute your columns)

=CALCULATE(countrows(projects),projects[status]=“Active”,Projects[rag]=“Red”)