Forum Discussion
Add column by specific text by dates
First, you need to extract the date part from the "Incident Start" column. You can create a new column in Power BI using DAX to extract the date:
Incident Date = DATE(YEAR([Incident Start]), MONTH([Incident Start]), DAY([Incident Start]))
After creating the "Incident Date" column, you can sort your data by this column and then by the "Incident Start" time. Here’s how you can do it:
In the Power Query Editor, select the "Incident Date" column.
Click on the "Sort Ascending" or "Sort Descending" button to sort the data by the date.
Next, select the "Incident Start" column.
Click on the "Sort Ascending" or "Sort Descending" button to sort the data by the time within each day.
Hi Manasi25 ,
I couldn't agree more with bhanu_gautam, but I have some more personal thoughts.
Firstly the Data type needs to be changed to Date/time.
You can sort the Incident Start column in Table view.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Add a New Column Based on Specific Text in "Work notes":
Action Taken by =
IF(
CONTAINSSTRING([Work notes], "took"),
"ECC took care",
IF(
CONTAINSSTRING([Work notes], "escalated"),
"Taken care by CX",
BLANK()
)
To sort the data by the dates mentioned in the "Work notes" column, you need to extract the date from the text and then sort the data based on this extracted date.
You can create a new column to extract the date from the "Work notes" column using DAX. Assuming the date format in the "Work notes" is consistent, you can use the following DAX formula:
Extracted Date =
DATE(
MID([Work notes], 1, 4),
MID([Work notes], 6, 2),
MID([Work notes], 9, 2)
)
After creating the "Extracted Date" column, you can sort your data by this column.
To handle duplicate entries, you can use Power BI's data transformation capabilities in Power Query Editor. You can group by the "Incident Start" or any other relevant column and then sort within each group by the "Extracted Date".
Go to Power Query Editor.
Select the column you want to group by (e.g., "Incident Start").
Use the "Group By" feature to group the data.
Within each group, sort the data by the "Extracted Date" column.
Thank you for quick reply.
I got first solutions.Thanks !
But for second query, i want to sort the data by the dates (each day) by "Incident Start" only 🙂First, you need to extract the date part from the "Incident Start" column. You can create a new column in Power BI using DAX to extract the date:
Incident Date = DATE(YEAR([Incident Start]), MONTH([Incident Start]), DAY([Incident Start]))
After creating the "Incident Date" column, you can sort your data by this column and then by the "Incident Start" time. Here’s how you can do it:
In the Power Query Editor, select the "Incident Date" column.
Click on the "Sort Ascending" or "Sort Descending" button to sort the data by the date.
Next, select the "Incident Start" column.
Click on the "Sort Ascending" or "Sort Descending" button to sort the data by the time within each day.Hello All
Sorry to reply late here.
The query is - I have 5 alerts in a day, which should be consider as 1, this alert is reporting daily and i want to count only 1 instead of 5 per day.
As per below on date 02/01/2025 I want alert of"A" considered as only 1 and remove duplicates from same col. Same on date 02/02/2025 alert of "A" should be considered count only 1 and remove duplicates. How can this be done ?Number Alert Date 1 A 02/01/2025 2 A 02/01/2025 3 A 02/01/2025 4 A 02/01/2025 5 B 02/01/2025 6 B 02/01/2025 8 B 02/01/2025 9 A 02/02/2025 10 A 02/02/2025
Hi Manasi25 ,
I couldn't agree more with bhanu_gautam, but I have some more personal thoughts.
Firstly the Data type needs to be changed to Date/time.
You can sort the Incident Start column in Table view.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
