Convert column into measure

[dantyson80]

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 · Accepted solution]

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]

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]

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]

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]

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]

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.