DAX: Adding a new column from an existing formula

[LABrowne]

Hi there,

I am building a live dashboard with an order table.

The below represents what I use to colour cells/row of the order table based on a certain set of conditions.

Background Cancellation Colours =

VAR CancelledOrders = CONTAINSSTRING( MAX (Orders[OrderNumber]), "Cancelled")

VAR UnderReviewOrders = CONTAINSSTRING( MAX (Orders[OrderNumber]), "Under Review") && TODAY() <= [Expiry Date Less 90 Days]

VAR HotOrders = [OrderHot] > 100 && Order[No. of Issues] >= 2 && TODAY() <= [Expiry Date Less 90 Days]

RETURN SWITCH(

    TRUE(),

    CancelledOrders, "Blue",

    UnderReviewOrders, "Red",

    HotOrders, "Orange"

)

I want to add a new column on the policy table actually puts each order into a category so it can be counted, analysed etc.

Below is a simple idea of what I want it to look like

OrderNumber	*NEED NEW COLUMN FORMULA?*
123 - Cancelled	Cancelled
12334 - Under Review	Under Review
2134	N/A

Hot would be for all orders > 100 & N/A would be for everything else that doesn't meet any 3 criteria.

Please let me know if you need any other advice

[LABrowne]

Perfect thanks! By any chance do you know how I would filter the results of the filter? e.g. is there a way to be able to filter the visual table based on measure results/values?

Kind regards,

Luke

[123abc]

Plz try this DAX:

\NewCategoryColumn =
ADDCOLUMNS (
Orders,
"Category",
SWITCH (
TRUE(),
CONTAINSSTRING( Orders[OrderNumber], "Cancelled"), "Cancelled",
CONTAINSSTRING( Orders[OrderNumber], "Under Review") && TODAY() <= Orders[Expiry Date Less 90 Days], "Under Review",
Orders[OrderHot] > 100 && Orders[No. of Issues] >= 2 && TODAY() <= Orders[Expiry Date Less 90 Days], "Hot",
"N/A"
)
)

This code adds a new column called "Category" to the Orders table. The SWITCH function is used to evaluate each order based on the specified conditions, and the corresponding category is assigned to the "Category" column.

You can adjust the conditions in the SWITCH statement based on your specific criteria. The last condition in the SWITCH statement, "N/A", will be applied if none of the previous conditions are met.

Make sure to replace the column names (Orders[OrderNumber], Orders[Expiry Date Less 90 Days], Orders[OrderHot], Orders[No. of Issues]) with your actual column names.

After creating this new column, you can use it in your dashboard for analysis and visualization.

[LABrowne]

Hi there,

I got the following error message: 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

The measures well don't work if they have 'Orders'. For example, Orders[OrderHot] needs to be [OrderHot] but I'm not too sure.  

[123abc · Accepted solution]

You're right; when creating a calculated column in Power BI, you need to reference columns without specifying the table name. Here's the corrected formula:

Category =
VAR CancelledOrders = CONTAINSSTRING( Orders[OrderNumber], "Cancelled")
VAR UnderReviewOrders = CONTAINSSTRING( Orders[OrderNumber], "Under Review") && TODAY() <= Orders[Expiry Date Less 90 Days]
VAR HotOrders = Orders[OrderHot] > 100 && Orders[No. of Issues] >= 2 && TODAY() <= Orders[Expiry Date Less 90 Days]
RETURN
SWITCH(
TRUE(),
CancelledOrders, "Cancelled",
UnderReviewOrders, "Under Review",
HotOrders, "Hot",
"N/A"
)

In this corrected formula, I removed the table prefix (Orders) from the column references, assuming that you are adding this calculated column to the Orders table. Make sure you add this formula to the "Orders" table.

If you're still encountering issues, please double-check the column names in your actual data model and replace them accordingly. If you provide more details about your data model or any specific error messages, I'll be happy to assist further.