Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LABrowne
Helper II
Helper II

DAX: Adding a new column from an existing formula

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 - CancelledCancelled
12334 - Under ReviewUnder Review
2134N/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

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

View solution in original post

4 REPLIES 4
LABrowne
Helper II
Helper II

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
Community Champion
Community Champion

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.

 

 

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.