Forum Discussion

cgreen1's avatar
cgreen1
Regular Visitor
4 years ago
Solved

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-Missin...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago
    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's avatar
    VahidDM
    4 years ago

    Hi cgreen1 

     

    As AlexisOlson  mentioned, IF statement has 3 Terms:

     

    Term Definition

    logical_testAny value or expression that can be evaluated to TRUE or FALSE.
    value_if_trueThe 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/

     

     

     

     

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi cgreen1 

 

As AlexisOlson  mentioned, IF statement has 3 Terms:

 

Term Definition

logical_testAny value or expression that can be evaluated to TRUE or FALSE.
value_if_trueThe 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/