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

Cperer 21 Reputation points
2023-08-28T17:32:41.98+00:00

Hello,

 

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

 

chat_peters_0-1693240445671.png

 

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. 

 

chat_peters_1-1693240661650.png

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
463 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,296 questions
{count} votes

Accepted answer
  1. Amira Bedhiafi 27,051 Reputation points
    2023-08-29T09:20:03.4133333+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 33,636 Reputation points Microsoft Employee
    2023-08-29T06:44:30.86+00:00

    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

    2 people found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.