Should and Should NOT Conditional Column

[awsiya]

am having a little trouble. I have a simple table with just 2 columns "Enrolled" and "Expected". I(having values 1,2, 2C, 3,4,5) in both rows ( am more concerned about the value "2C: and 5 )

i NEED to add a conditional column in PowerBI Data tab where I want to highlight every time someone enrolls into "2C" and they are not expected to (and the other way around) which means if the value in Expected is 2C but is not 2C in enrolled, answer should be like "SHOULD be 2C but isnt") AND Same case with value 5 and should yield a similar result "Should be 5 but isnt)

and the other condition is the opposite- IF ENROLLEd COLUMNS IS "2C" and they are not expected (means the expected column is not "2C" then it should reflect "Should NOT be 2C but is) and same case with value 5 and should yield a similar result "Should NOT be 5 but isnt)

also, IF this "2C" in the expected column has the corresponding Enrolled Row value empty, I might need to display it like "Expected 2C has Enrolled empty")

same needs to be done for "5", if 5 in the expected column has the corresponding Enrollow row empty and shows "Expected 5 has enrolled empty".

AND IF BOTH rows are same (5 matching corrsopoding 5 as well as 2C matching with corrospoding 2C) (as in last row e.g), then we need to highlight something "Expected = Enrolled". I have tried to represent the table and outcome in the screenshot below

rest all as "Not 2C OR 5"

[audreygerred · Accepted solution]

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.

[ryan_mayu · Accepted solution]

@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"))))

[awsiya]

Thank you indeed Ryan 🙂

[ryan_mayu]

you are welcome

[audreygerred · Accepted solution]

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]

Thank you very much 🙂 🙂

[audreygerred]

You're very welcome!