How to count if at least one value meets criteria, then add to a count

[Bastienlin_]

Hello,

Here is an example of a sample of my data:

City	Question	Answer
Paris	1	Yes
Paris	2	Yes
Paris	3	Yes
London	1	Yes
London	2	No
London	3	No
Madrid	1	Yes
Madrid	2	Yes
Madrid	3	No

I need to count the distinct number of countries that has at least one "No" as answer in the selected questions (Here, question 1, 2 & 3).

The output should be 2 there, for London and Madrid having at least one "No" for these questions.

Thank you for your help !

[Anonymous · Accepted solution]

Hi @Bastienlin_ ,

Please try to create a measure with below dax formula and add it to card visual

Measure =
VAR tmp =
    SUMMARIZE (
        'Table',
        'Table'[City],
        'Table'[Answer],
        "Count NB", COUNT ( 'Table'[Question] )
    )
VAR tmp1 =
    FILTER ( tmp, [Answer] = "No" && [Count NB] >= 1 )
RETURN
    COUNTROWS ( tmp1 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bastienlin_]

Hello @Anonymous, thank you for your answer.

I also tried in parallel a formula and I think it works too:

Measure = CALCULATE(DISTINCTCOUNT(Table[City]), FILTER(ALL(Table),[Question] in {"1", "2", "3",}),Table[Answer] = "No")

This measure seems simplier, what do you think ?

Best regards,
Bastien

[Anonymous]

Hi @Bastienlin_ ,

For measure, the simpler the better, as long as you are sure to get the right result.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.