Creating a measure on text table

[lastnn30]

Hi

I have a table called XYZ with one column called ABC. IN that column I have home addresses like this

SouthWest Road, 1234

So I want to write a measure to find the location of "," so I can use that number later to seprate the street name from street number. But PBI wont accept to create this measure

MyMeasure = search(" ", XYZ[ABC])

But I can create a "New Column" using the same above DAX formula. Why is that? Thank you so much.

[djurecicK2 · Accepted solution]

Hi @lastnn30 ,

 This is because a measure is normally used to do some type of aggregation and calculated column already has the row context to know what record you are referring to and does not require an aggregation like Sum, Max, etc. Here is some additional information: https://endjin.com/blog/2022/04/measures-vs-calculated-columns-in-dax

For the situation you describe, it seems like calculated column is the way to go.