Result of measure is correct, but it's flagging wrongly.

[ju_jacobowiski]

Hey, guys. I've written a measure to check the number of cases that meet a requirement:

questions_block_N1_V2 = 

IF([%_compliance_trabalhista_red] <> 1,

CALCULATE(
DISTINCTCOUNT('5_es_individual_compliance'[id_question]),
'5_es_individual_compliance'[question_class] IN {"red"},
'5_es_individual_compliance'[compliance_status] IN {"False"}, 
'5_es_individual_compliance'[topic_txt] IN {"Trabalhista", "Empregados Próprios", "Consórcio", "Terceiros", "Prestadores de Serviço", "Prevenção de Riscos e Acidentes", "Solo", "Plantio", "MIP", "Adubação", "Gestão", "Queima", "Agroquímicos", "Recursos hídricos", "Floresta"}),

IF([%_compliance_trabalhista_purple] < 0.75,

CALCULATE(
DISTINCTCOUNT('5_es_individual_compliance'[id_question]),
'5_es_individual_compliance'[question_class] IN {"purple"},
'5_es_individual_compliance'[compliance_status] IN {"False"},
'5_es_individual_compliance'[topic_txt] IN {"Trabalhista"}),

IF([%_compliance_trabalhista_blue] < 0.5, 

CALCULATE(
DISTINCTCOUNT('5_es_individual_compliance'[id_question]),
'5_es_individual_compliance'[question_class] IN {"blue"},
'5_es_individual_compliance'[compliance_status] IN {"False"},
'5_es_individual_compliance'[topic_txt] IN {"Trabalhista"}))))

The result I get from this measure is "2" for this case, which is correct, as shown in the image highlighted:



However, when I add the measure to the list of question to flag which one represents these "2", I got the follow:



The final result is correct, but it flags the 4 questions in the group "3. Roxa", which it shouldn't since they are in a group where the % is > then 75%. Not sure what I'm doing wrong. 

Thanks in advance!

[v-juanli-msft]

Hi @ju_jacobowiski 

Try this measure instead

M =
IF (
    [%_compliance_trabalhista_blue] < 0.5,
    CALCULATE (
        DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
        '5_es_individual_compliance'[question_class] IN { "blue" },
        '5_es_individual_compliance'[compliance_status] IN { "False" },
        '5_es_individual_compliance'[topic_txt] IN { "Trabalhista" }
    ),
    IF (
        [%_compliance_trabalhista_purple] < 0.75,
        CALCULATE (
            DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
            '5_es_individual_compliance'[question_class] IN { "purple" },
            '5_es_individual_compliance'[compliance_status] IN { "False" },
            '5_es_individual_compliance'[topic_txt] IN { "Trabalhista" }
        ),
        IF (
            [%_compliance_trabalhista_red] <> 1,
            CALCULATE (
                DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
                '5_es_individual_compliance'[question_class] IN { "red" },
                '5_es_individual_compliance'[compliance_status] IN { "False" },
                '5_es_individual_compliance'[topic_txt]
                    IN {
                    "Trabalhista",
                    "Empregados Próprios",
                    "Consórcio",
                    "Terceiros",
                    "Prestadores de Serviço",
                    "Prevenção de Riscos e Acidentes",
                    "Solo",
                    "Plantio",
                    "MIP",
                    "Adubação",
                    "Gestão",
                    "Queima",
                    "Agroquímicos",
                    "Recursos hídricos",
                    "Floresta"
                }
            )
        )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ju_jacobowiski]

Hi, Maggie! Really appreciate the answer.

It sorts partially. The results are still correct, but now it ONLY flags the questions that meet the first condition (in the group blue), but then if I change the scenario, and there are questions that meet the 2nd or the 3rd condition, it doesn't flag them. Maybe I should and an OR in the function?

Gonna try!

[akohli]

I think it's the syntax that you have used in your `countdistinct`. experiment with `&` or `&&` or `and` or `|` or `or` and see if you can get the value you want. Sorry, it's hard to know what you actually want.

[ju_jacobowiski]

@v-juanli-msft 

I was suggested to break the measure into 3 dimensions, to check whether it was working individually or not:

Metric1_

M_blue = 
IF(
    [%_compliance_trabalhista_blue] < 0.5,
    CALCULATE (
        DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
        '5_es_individual_compliance'[question_class] IN { "blue" },
        '5_es_individual_compliance'[compliance_status] IN { "False" },
        '5_es_individual_compliance'[topic_txt] IN { "Trabalhista" }),0)

Metric2_

M_purple = 

IF (
        [%_compliance_trabalhista_purple] < 0.75,
        CALCULATE (
            DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
            '5_es_individual_compliance'[question_class] IN { "purple" },
            '5_es_individual_compliance'[compliance_status] IN { "False" },
            '5_es_individual_compliance'[topic_txt] IN { "Trabalhista" }
        ), 0)

Metric3_

M_red = 
IF (
            [%_compliance_trabalhista_red] <> 1,
            CALCULATE (
                DISTINCTCOUNT ( '5_es_individual_compliance'[id_question] ),
                '5_es_individual_compliance'[question_class] IN { "red" },
                '5_es_individual_compliance'[compliance_status] IN { "False" },
                '5_es_individual_compliance'[topic_txt]
                    IN {
                    "Trabalhista",
                    "Empregados Próprios",
                    "Consórcio",
                    "Terceiros",
                    "Prestadores de Serviço",
                    "Prevenção de Riscos e Acidentes",
                    "Solo",
                    "Plantio",
                    "MIP",
                    "Adubação",
                    "Gestão",
                    "Queima",
                    "Agroquímicos",
                    "Recursos hídricos",
                    "Floresta"
                }
            ),0)
    

Here the individual results per metric. The pattern continues, look at this case:



Even though the results are still fine, it keeps flagging the questions. 

@akohli you answered in another topic suggesting to break down the measure (it was duplicated, I'm gonna delete that one and keep all here).