Convert data from one form to another

[Anonymous]

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.

[d_gosbell]

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")

[Anonymous]

Dear @d_gosbell ,

Can you please elaborate step by step, Because i am a learner to power BI

[d_gosbell · Accepted solution]

1. first click on your column with the 3 statuses,
2. then in the Transform ribbon click on the "Split Column" button
3. then chose the "by Delimiter" option and choose "/" as your delimiter (Power Query should detect this)
4. then click on ok, this should split your column into 3 numeric values
5. rename the 3 new columns to "Comp", "Pend" and "Overdue"
6. then click on the "Add Column" ribbon
7. then click on "Custom Column"
8. 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"

[Anonymous]

Facing below error on doing the above procedure

Expression.Error: The field 'Pend' of the record wasn't found.
Details:
Report Number=Obs-2017-000182
Observation Date=1/2/2017 11:23:00 PM
Observation Time=23:23 (UTC+05:30) IST
Observation Method=Safety Walkaround
Site - Location No=43685
Division - Divisional Region Adhesive Technologies=MEA
Region=Asia
Country=India
Location Type=active
Site=Chennai
Division=Adhesive Technologies
Sub-division=AT
Employee function=
Team Member=Prabhu Thiagarajan
Comments=Near Packing material storage area in Extruder cooling line water leakage
Comp=1
pend=0
Overdue=0
Created By=Prabhu Thiagarajan
Created Date=1/2/2017 5:57:17 PM
Observation Location=Extruder shop floor
Safety Walkaround - Walkaround Focus=Safety
STOP Audit *(STOP is trademark of DuPont) - Audit Focus=
Safety Observation - Description :(Include name of the original reporter if applicable)=
SUSA Conversation - Location (Area):=
SUSA Conversation - Observed Type=
SUSA Conversation - Probability:=
SUSA Conversation - Consequence:=
Result Number=
Comment=
Category=
Sub Category=
Class of Observation=
Action Item Number:=Obs-2017-000182_1
Action Item Description=Near Packing material storage area in Extruder cooling line water leakage - Leakage to be arrested as soon as possible
Assigned To=Suresh Mungara
Target Date:=1/31/2017
Completion Date:=1/18/2017
Inactive-Does the observation have:=
Index=1

[d_gosbell]

---

@Anonymous wrote:

Facing below error on doing the above procedure

Expression.Error: The field 'Pend' of the record wasn't found.

---

That most likely means you called the second column something different