Forum Discussion

dantyson80's avatar
dantyson80
Frequent Visitor
4 years ago
Solved

Convert column into measure

I have a ZIP code column that needs cleaned up to use for a map visual. Some of the zip codes are Canadian (start with letters) and the rest are USA. Some of the US-based ZIP codes are missing leadin...
  • dantyson80's avatar
    dantyson80
    4 years ago

    I eventually figured out a solution, which is a bit of a workaround unfortunately. In Power Query, I first created a Custom Column that shows me number of characters in the ZIP column:

    Then I was able to create a query that adds the leading zero if the length was less than 5:

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

I am not sure how your desired outcome of the visualization looks like, but please try the below whether it suits your requirement.

 

RealZipCode =
SWITCH (
    TRUE (),
    LEN ( SELECTEDVALUE ( 'Table'[ZipCode] ) ) = 5, SELECTEDVALUE ( 'Table'[ZipCode] ),
    LEN ( SELECTEDVALUE ( 'Table'[ZipCode] ) ) = 4, "0" & SELECTEDVALUE ( 'Table'[ZipCode] ),
    LEN ( SELECTEDVALUE ( 'Table'[ZipCode] ) ) = 3, "00" & SELECTEDVALUE ( 'Table'[ZipCode] )
)
dantyson80's avatar
dantyson80
Frequent Visitor
4 years ago

Hello Jihwan,

I tried this and it doesn't work. I was able to enter the formula without problems but when I check the RealZipCode measure, it doesn't populate the map at all. Any ideas why this is the case? Thanks.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not 100% sure without seeing your pbix file, but please try to check data type and category type of your column.

    I suggest searching "power bi column category type zip code" in Edge or Google.

    Thanks.

    • dantyson80's avatar
      dantyson80
      Frequent Visitor
      4 years ago

      The Data category is "Postal code" for ZipCode. I don't think I can send the file due to a large amount of sensitive information. Are there additional screenshots that might help?

      • dantyson80's avatar
        dantyson80
        Frequent Visitor
        4 years ago

        Can anyone provide further assistance? I'm still amazed how seemingly difficult it is to get DAX to recognize a column as a measure. Unbelievable.