DAX IF Statement

[cgreen1]

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.

[AlexisOlson · Accepted solution]

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".

[VahidDM · Accepted solution]

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/

[AlexisOlson · Accepted solution]

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".