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 leading zeros. I mention the combined US and Canadian zip codes because some options to add leading zeros seem to be based on integers. I found a DAX formula that will allow me to add the leading zeros, but I figured out that the ZIP code has to be a measure (not a column) for it to work. Here is the formula:

RealZipCode = 
SWITCH(
    TRUE(),
    LEN('Table'[ZipCode]) = 5, 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 4, "0" & 'Table'[ZipCode],
    LEN('Table'[ZipCode]) = 3, "00" & 'Table'[ZipCode]
)

The only options that show up are measures. It seems that there is no easy way to convert a column to a measure, which if it's true, seems very strange. Please help. Thank you.

  • 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:

     

6 Replies

  • 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

    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: