Should and Should NOT Conditional Column

Hello! You can definitely do this in DAX, but I would suggest to add in Power Query via the advanced editor instead. Applying transformations in Power Query can improve query performance by reducing the amount of data loaded into Power BI.

You can use the below to update what is currently in your M code:

let
Source = <your_source_data>,
CustomColumn = Table.AddColumn(Source, "ConditionalColumn", each
if [Enrolled] = "2C" and [Expected] <> "2C" then "SHOULD NOT be 2C"
else if [Enrolled] = "5" and [Expected] <> "5" then "SHOULD NOT be 5"
else if [Expected] = "2C" and [Enrolled] <> "2C" then "SHOULD BE 2C but isn't"
else if [Expected] = "5" and [Enrolled] <> "5" then "SHOULD BE 5 but isn't"
else if [Enrolled] = "2C" and [Expected] = "2C" then "Expected = Enrolled"
else if [Enrolled] = "5" and [Expected] = "5" then "Expected = Enrolled"
else if [Expected] = "2C" and ([Enrolled] = null or [Enrolled] = "") then "Expected 2C has Enrolled Empty"
else if [Expected] = "5" and ([Enrolled] = null or [Enrolled] = "") then "Expected 5 has Enrolled Empty"
else "Not 2C or 5"
)
in
CustomColumn

Below are the results I get using the above code:

**In general, you should use Power Query for data preparation and transformation tasks, especially when you need to reshape or clean your data before it enters the data model. This approach keeps your data model clean and efficient.

Use DAX calculated columns when you need to create new columns that involve complex calculations or derive values from existing columns, and those calculations are best suited for in-memory processing. Keep in mind that calculated columns consume memory, so avoid overusing them for performance reasons.

awsiya 

you can try this

Column = IF('Table'[Enrolled ] in {"5","2C"}&&'Table'[Enrolled ]='Table'[Expected],"Expected = Enrolled", if('Table'[Enrolled ] in {"5","2C"}&&'Table'[Enrolled ]<>'Table'[Expected],"SHOULD NOT BE " & 'Table'[Enrolled ],if('Table'[Expected] in {"5","2C"}&&'Table'[Enrolled ]<>'Table'[Expected],"SHOULD BE " & 'Table'[Expected]&" but isn't",if(not('Table'[Enrolled ] in {"2C","5"} )&& not('Table'[Expected] in {"2C","5"}),"Not 2C OR 5"))))