Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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" } ) ) ) )
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!
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.
@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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |