Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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
Solved! Go to 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.
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
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.
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |