Calculated DAX column with multiple If statements

[andronachealin]

Hi all,

I would like to create a calculated column using DAX, titled Curriculum Status, that will apply the following logic:

• For each User ID (column C), if all course IDs in column B are mapped to the curriculum in column A and if they have a Completed Course Status (column D) -> then add a Completed value in column E.
• If the conditions above are not met -> then add a Incomplete value in column E.

The end results should look like this:

[wdx223_Daniel]

=VAR _course=CALCULATETABLE(VALUES(sample[Course ID]),ALLEXCEPT(sample,sample[User ID])) VAR  _curri=CALCULATETABLE(VALUES(sample[Curriculumn ID]),ALL(sample),sample[Course ID] IN _course) VAR _status=CALCULATETABLE(VALUES(sample[Course Statues]),ALL(sample),sample[Curriculum ID] IN _curri,sample[Course Status]<>"Completed") RETURN IF(COUNTROWS(_status)>0,"Incompleted","Completed")

[lbendlin · Accepted solution]

I didn't understand this part " if all course IDs in column B are mapped to the curriculum in column A "  and it doesn't seem to matter for your desired result.

Curriculum Status = 
var s = CALCULATETable(values('Table'[Course Status]),ALLEXCEPT('Table','Table'[User ID]))
return if(concatenatex(s,'Table'[Course Status],",")="Completed",s,"Incomplete")

[andronachealin]

@lbendlin True. I did not really need that condition.
Thanks for the solution. Works like a charm. Much appreciated.

[mahoneypat]

Please try this column expression

Status =
VAR vIncompleteRows =
    CALCULATE (
        COUNTROWS ( Table ),
        ALLEXCEPT (
            Table,
            Table[User ID],
            Table[Curriculum ID]
        ),
        Table[Course Status] <> "Completed"
    )
RETURN
    IF (
        vIncompleteRows > 0,
        "Incomplete",
        "Completed"
    )

Pat