topic Re: DAX: Adding a new column from an existing formula in DAX Commands and Tips

DAX: Adding a new column from an existing formula

LABrowne — Tue, 28 Nov 2023 12:22:04 GMT

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

Re: DAX: Adding a new column from an existing formula

123abc — Tue, 28 Nov 2023 12:30:12 GMT

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.

Re: DAX: Adding a new column from an existing formula

LABrowne — Tue, 28 Nov 2023 12:44:18 GMT

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.

Re: DAX: Adding a new column from an existing formula

123abc — Tue, 28 Nov 2023 13:10:51 GMT

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.

Re: DAX: Adding a new column from an existing formula

LABrowne — Tue, 28 Nov 2023 13:48:38 GMT

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