How to use the format string function in the column properties to do custom formatting

Question

Hello,

 

I have a date_key column with data type whole number and formatted as Text as shown below.

 

 

I want to apply this same logic to the date_key column in another table for my measure to work. I don't remember how to use the custom formatting and what do I put in the format string section. Does this use power query or dax. 

 

Answer 1

The date_key column you mentioned, which is formatted as a whole number and interpreted as Text, presumably represents dates in a format like YYYYMMDD (for example 20230829 for August 29, 2023). You wish to custom format this column to make it appear like a typical date format.

You will be using DAX (Data Analysis Expressions) to achieve this custom formatting. You don't need Power Query for this task.

Create a new calculated column in your table with the following DAX formula:

   CustomDate = 

   FORMAT(DATE(VALUE(LEFT([date_key], 4)),  // Year

                 VALUE(MID([date_key], 5, 2)),  // Month

                 VALUE(RIGHT([date_key], 2))),  // Day

           "MM/DD/YYYY") 

This formula breaks down the whole number into its year, month, and day components, then uses the FORMAT function to represent it in the "MM/DD/YYYY" format.

In case you're asking about the 'Format String' option that's present in the modeling tab, that's for direct custom formatting. But given your requirement, the transformation of a YYYYMMDD integer into an actual date format is best achieved with DAX as described above.

---

UPDATE :

In SSAS Tabular, data types are inferred during the import process. You don't change data types like you do in Power BI Desktop. If your column data type has been inferred incorrectly, you often have to adjust it at the source or during the data import process.

While the main intent of DAX is for calculations, you can create a new calculated column representing your original column as text. You can do this using the FORMAT function:

`DateKeyAsText = FORMAT([date_key], "")`

This formula will convert the date_key into a text representation in a new column.

Once you have your data in SSAS Tabular, you can further adjust column properties and formats using Tabular Model Scripting Language (TMSL) or Tabular Object Model (TOM), but these are more advanced topics and might be beyond what you're trying to achieve.

If you're trying to change the data type of an existing column to text in the Tabular Model, you would likely have to adjust this at the data source level or during your data loading process. Remember, SSAS Tabular is optimized for fast performance, and its design assumes that much of the data shaping and transformation happens before data gets into the model.

Answer 2

Hi Cperer ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

From the description of your query it seems you want to know how to use the format string function in the column properties to do custom formatting.

You can use Custom formats using format function in DAX query

For more details on the implementation and examples, kindly visit the following documentation: FORMAT function in DAX

Hope it helps. Please let us know how it goes. Thankyou