Forum Discussion
Calculated DAX column with multiple If statements
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:
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")
4 Replies
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
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")lbendlin True. I did not really need that condition.
Thanks for the solution. Works like a charm. Much appreciated.
=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")
