DAX Help

[Anonymous]

Hi,

2019-03-01-PID 004-327-659 - 12851 Bathgate Way, Richmond.pdf

I have a column of unstructured data as mentioned above, and i was wondering if there is a way to create a new column and only display the data in red.  All the rows have the words "PID" in them.

thanks,

[yelsherif]

The format of required part is PID nnn-nnn-nnn
If this is always the case, then you can add a calculated column with the following formula
newColumn =

var leftPosition = FIND("PID",[sourceColumn])

return

mid(theText,leftPosition,15)

[yelsherif]

Correction..

newColumn =

var leftPosition = FIND("PID",[sourceColumn])

return

mid([sourceColumn],leftPosition,15)

[Anonymous]

it didn't work.  

---

@yelsherif wrote:

Correction..

newColumn =

var leftPosition = FIND("PID",[sourceColumn])

return

mid([sourceColumn],leftPosition,15)

---

---

@yelsherif wrote:

Correction..

newColumn =

var leftPosition = FIND("PID",[sourceColumn])

return

mid([sourceColumn],leftPosition,15)

---

---

@yelsherif wrote:

Correction..

newColumn =

var leftPosition = FIND("PID",[sourceColumn])

return

mid([sourceColumn],leftPosition,15)

---

[yelsherif · Accepted solution]

The error happens if Name2 does not contain "PID"
So we can modify the formula to return blank if the search value is not found

pidcolumn =

var leftPosition = FIND("PID",Table1[Name2],1,0)
return

   if(leftPosition>0,mid(theText,leftPosition,15),blank())

[Anonymous]

thank you so much.  it worked