Forum Discussion
DAX IF Statement
May someone please explain, in detail, what the following formula is asking for?
Total Score = IF(CALCULATE(COUNTA(Form_V[Answer]),DISTINCT(Form_V[FORM_ID]), FORM_V[Answer] = "No") = 0, "No-Missing", "Missing")
Thanks.
Total Score = IF ( CALCULATE ( COUNTA ( Form_V[Answer] ), DISTINCT ( Form_V[FORM_ID] ), FORM_V[Answer] = "No" ) = 0, "No-Missing", "Missing" )This counts the "No" answers and compares the count to zero. If there are 0 "No" answers, it returns "No-Missing", otherwise "Missing".
Hi cgreen1
As AlexisOlson mentioned, IF statement has 3 Terms:
Term Definition
logical_test Any value or expression that can be evaluated to TRUE or FALSE. value_if_true The value that's returned if the logical test is TRUE. value_if_false (Optional) The value that's returned if the logical test is FALSE. If omitted, BLANK is returned. In your formula:
Total Score = IF ( CALCULATE ( COUNTA ( Form_V[Answer] ), DISTINCT ( Form_V[FORM_ID] ), FORM_V[Answer] = "No" ) = 0, "No-Missing", "Missing" )
logical_test:CALCULATE (
COUNTA ( Form_V[Answer] ), --The COUNTA function counts the number of cells in a column that are not empty.
DISTINCT ( Form_V[FORM_ID] ), --Returns a one-column table that contains the distinct values from the specified [FORM_ID] column. In other words, duplicate values are removed and only unique values are returned.
FORM_V[Answer] = "No" -- filter to remove Yes from [Answer] column and keep No
) -- the output is the number of No answers in the FORM_V[Answer] column
= 0 -- if it's Zero then value_if_true will return, otherwise, if the output is not Zero, it will return value_if_false
value_if_true: "No-Missing"value_if_false: "Missing"
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
2 Replies
Total Score = IF ( CALCULATE ( COUNTA ( Form_V[Answer] ), DISTINCT ( Form_V[FORM_ID] ), FORM_V[Answer] = "No" ) = 0, "No-Missing", "Missing" )This counts the "No" answers and compares the count to zero. If there are 0 "No" answers, it returns "No-Missing", otherwise "Missing".
Hi cgreen1
As AlexisOlson mentioned, IF statement has 3 Terms:
Term Definition
logical_test Any value or expression that can be evaluated to TRUE or FALSE. value_if_true The value that's returned if the logical test is TRUE. value_if_false (Optional) The value that's returned if the logical test is FALSE. If omitted, BLANK is returned. In your formula:
Total Score = IF ( CALCULATE ( COUNTA ( Form_V[Answer] ), DISTINCT ( Form_V[FORM_ID] ), FORM_V[Answer] = "No" ) = 0, "No-Missing", "Missing" )
logical_test:CALCULATE (
COUNTA ( Form_V[Answer] ), --The COUNTA function counts the number of cells in a column that are not empty.
DISTINCT ( Form_V[FORM_ID] ), --Returns a one-column table that contains the distinct values from the specified [FORM_ID] column. In other words, duplicate values are removed and only unique values are returned.
FORM_V[Answer] = "No" -- filter to remove Yes from [Answer] column and keep No
) -- the output is the number of No answers in the FORM_V[Answer] column
= 0 -- if it's Zero then value_if_true will return, otherwise, if the output is not Zero, it will return value_if_false
value_if_true: "No-Missing"value_if_false: "Missing"
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/