Forum Discussion
Convert data from one form to another
Dear All,
I have a report with status (as in values) which means it is in format of Action Status : Comp/ Pending/ Overdue, here, if number other than zero is present then that means the point has that many number of sub tasks (4/0/0 - means 4 subtasks in completed stage. How do I write dax code than using conditional formatting.
Simply: when there is a number other than zero in the format of Comp/ Pending/ Overdue, then that many sub tasks at its stage.
- first click on your column with the 3 statuses,
- then in the Transform ribbon click on the "Split Column" button
- then chose the "by Delimiter" option and choose "/" as your delimiter (Power Query should detect this)
- then click on ok, this should split your column into 3 numeric values
- rename the 3 new columns to "Comp", "Pend" and "Overdue"
- then click on the "Add Column" ribbon
- then click on "Custom Column"
- and paste in the following code
if [Comp] = 0 and [Pend]= 0 and [Overdue] = 0 then "No Actions Defined" else if [Comp] > 0 and [Pend] > 0 then "Partially Complete" else if [Comp] > 0 then "Complete" else if [Pend] > 0 then "Pending" else if [Overdue] > 0 then "Overdue" else "Other"
5 Replies
- d_gosbell
Super User
This looks like Power Query code, not DAX. The other option here would be to do a split transform using "/" to split the values in order to create 3 separate columns for each of Comp, Pending & Overdue. Then you could do numeric > 0 checks instead of having to type out. The only problem is that to check 2 columns as in the "Partial" case you would have to edit the expression code by hand as the Wizard page does not have a way to generate and "AND" condition
= Table.AddColumn(#"Renamed Columns", "Custom", each
if ([Comp] > 0) and ([Pend] > 0) then "Partial"
else if [Comp] > 0 then "Complete"
else if [Pend] > 0 then "Pending"
else "Overdue")- d_gosbell
- first click on your column with the 3 statuses,
- then in the Transform ribbon click on the "Split Column" button
- then chose the "by Delimiter" option and choose "/" as your delimiter (Power Query should detect this)
- then click on ok, this should split your column into 3 numeric values
- rename the 3 new columns to "Comp", "Pend" and "Overdue"
- then click on the "Add Column" ribbon
- then click on "Custom Column"
- and paste in the following code
if [Comp] = 0 and [Pend]= 0 and [Overdue] = 0 then "No Actions Defined" else if [Comp] > 0 and [Pend] > 0 then "Partially Complete" else if [Comp] > 0 then "Complete" else if [Pend] > 0 then "Pending" else if [Overdue] > 0 then "Overdue" else "Other"