Graphed LogoGraphed
Login

How to Change Data Type in Power BI Using DAX

Cody Schneider•

When you import data into Power BI, it doesn't always arrive in the perfect format. You might find sales figures stored as text, order quantities as decimals, or dates treated as plain strings, which can stop your calculations and visualizations dead in their tracks. This article will show you how to quickly correct these data types using Power BI's DAX formula language, giving you more flexibility and control directly within your data model.

Why Not Just Use Power Query?

If you're familiar with Power BI, your first thought is probably, "Why not change the data type in Power Query?" And that's a great instinct. Power Query (the 'Transform Data' window) is absolutely the best place for permanent data type changes during your initial data setup and cleaning. It's robust and applies the changes at the source of your data load.

However, there are specific scenarios where using DAX for data type conversion is either necessary or more efficient:

  • Inside Measures: The most significant reason is that you can't use Power Query within a DAX measure. Measures are calculated on the fly based on user interactions (like filters or slicers). You might need to convert a text value to a number as one step in a larger, dynamic calculation. For example, averaging user survey scores that were input as text ("1", "2", "3").

  • When Creating Calculated Columns: While you can set a calculated column's data type after it's created, writing the conversion function directly into the formula makes your intent clear. This makes the code self-documenting and easier for others (or your future self) to understand without clicking through menus.

  • For Quick Experimentation: Sometimes, you just need a temporary calculation for analysis and don't want to go through a full Power Query data refresh, which can be slow on large datasets. Using a DAX calculated column provides an immediate result.

  • Handling Dynamic Inputs: In more advanced scenarios, especially when dealing with data from parameters or disconnected tables, DAX provides the flexibility to handle data type conversions based on specific logical conditions within the model itself.

A Quick Guide to Core DAX Data Types

Before jumping into the functions, let's briefly review the primary data types DAX operates with. Understanding what you are converting to is just as important as knowing how to convert.

  • Integer: Whole numbers, positive or negative (e.g., 10, -50, 483).

  • Decimal: Numbers that can have decimal places (e.g., 19.99, -0.25). This is a floating-point number type.

  • Currency: A fixed decimal number type that is optimized for financial calculations to avoid the minor rounding errors that can sometimes occur with floating-point numbers. It has a fixed precision of four decimal places.

  • Date/Time: Stores both date and time information in a special format that allows for date-based calculations.

  • String: This is the official name for a text value (e.g., "John Smith", "SKU-123", "New York").

  • Boolean: A simple logical value that can only be TRUE or FALSE.

Converting to Numbers: VALUE(), INT(), and More

One of the most common issues is numeric data that has been incorrectly formatted as text. Maybe your sales data looks like "$1,495.50" or an ID column is "007". To use this data in calculations, you first need to convert it to a legitimate number format.

Using the VALUE Function

The VALUE() function is your go-to tool for converting a text string that looks like a number into a proper numeric data type. It's smart enough to understand common formats, including currency symbols and commas.

Let's say you have a 'Sales' table with a column called 'Revenue (Text)' containing values like "$1,200.75". To do any math, you need a numeric version. You can create a new calculated column with this formula:

Revenue (Numeric) = VALUE('Sales'[Revenue (Text)])

After creating this column, Power BI will treat it as a number, allowing you to sum, average, or use it in any mathematical measure. However, be careful. If a row contains text that can't be recognized as a number (e.g., "N/A" or "Pending"), the VALUE() function will return an error for that row, which can break your visuals. It's often wise to wrap it in an error-handling function like IFERROR() (more on that later).

For More Specificity: INT() and DECIMAL()

Sometimes you need more control over the output. In these cases, INT() and DECIMAL() are useful.

  • INT() converts a value to an integer by rounding down to the nearest whole number. This is perfect for situations where you expect a whole number and want to truncate any accidental decimals.

  • DECIMAL() forcefully converts a value into the decimal data type.

For example, if you have an 'Inventory' table with a 'Stock Count (Text)' column, you could safely convert it to an integer:

Stock Count (Integer) = INT('Inventory'[Stock Count (Text)])

The main takeaway here is that VALUE() is your flexible workhorse, while INT() is for when you explicitly need a whole number.

Converting to Text: The Indispensable FORMAT() Function

Just as often, you will need to do the opposite: turn a number or date into a specific text format. This is extremely useful for creating clear chart labels, titles, or concatenating values into dynamic text strings.

The FORMAT() function is the single most powerful tool for this purpose. It takes a value as its first argument and a format code as its second argument.

Formatting Numbers as Text

Imagine you have a measure called [Total Revenue] that returns a number. You could create dynamic card titles or tooltips by formatting this number as text.

To format as currency:

Formatted Revenue = FORMAT([Total Revenue], "$#,##0.00")

This would turn 12345.678 into the text string "$12,345.68". Another simple approach is to use the built-in format strings:

Simple Formatted Currency = FORMAT([Total Revenue], "Currency")

To format as a percentage:

Profit Margin Pct = FORMAT([Margin], "0.0%")

This would take a decimal like 0.253 and turn it into the text string "25.3%". Crucially, remember the output of FORMAT() is always text. You cannot perform further mathematical operations on it.

Formatting Dates as Text

FORMAT() also excels at pulling dates apart or reassembling them into custom text strings. This is incredibly useful for chart axes or slicers where you need specific date labels.

Let's say you have an 'Orders'[OrderDate] column. You can create new columns with text formats:

Order Month Name = FORMAT('Orders'[OrderDate], "mmmm") # Returns "January", "February", etc.Order Month & Year = FORMAT('Orders'[OrderDate], "mmm yyyy") # Returns "Jan 2023", "Feb 2023", etc.

Converting to Dates: DATEVALUE() and DATE()

It's alarmingly common for dates to be imported as text strings like "12/25/2023" or "2023-12-25". To perform any time-intelligence calculations (like YTD or MTD), you must first convert these strings into a true date data type.

Using the DATEVALUE Function

The DATEVALUE() function is designed to interpret a date stored as text and convert it into Power BI's datetime format.

If your table 'Shipments' has a column 'ShipDate (Text)', you can create a proper date column like this:

ShipDate (Date Type) = DATEVALUE('Shipments'[ShipDate (Text)])

A word of caution: DATEVALUE() is highly dependent on your system's regional settings. If your text is formatted as "DD/MM/YYYY" but your machine uses "MM/DD/YYYY", the function can produce errors or silently misinterpret the dates. For this reason, it's often more reliable to handle initial date conversions in Power Query, where you can explicitly specify the locale of the source format.

Building a Robust Date with the DATE() Function

A much safer method is the DATE() function, especially if your date components (year, month, and day) are in separate numeric columns.

The DATE() function takes three numbers as arguments: Year, Month, and Day.

If you have an 'OrderLog' table with 'Year', 'Month', and 'Day' columns, you can construct a foolproof date column:

Full Order Date = DATE('OrderLog'[Year], 'OrderLog'[Month], 'OrderLog'[Day])

This approach isn't tied to text formats and is internationally robust, making it the superior choice when your data is structured this way.

Best Practices for DAX Data Type Conversion

Keep these tips in mind as you work with DAX conversion functions:

  • Default to Power Query: For your base data preparation, always perform your primary data type conversions in Power Query. It's more efficient for source data and easier to troubleshoot. Use DAX for the specific scenarios we've covered.

  • Always Double-Check the Result: After you create a calculated column, click on it and look at the "Column tools" ribbon at the top. This will show you the exact data type that Power BI has assigned, letting you confirm your function worked as expected.

  • Use IFERROR() for Protection: When converting text to numbers or dates, wrap your formula in the IFERROR() function to gracefully handle problem rows that could otherwise break your visuals. You can have them default to 0, BLANK(), or another value.

  • Understand Implicit vs. Explicit Conversion: Sometimes DAX will automatically (implicitly) convert types for you, like when you add a number and a numeric text string. Don't rely on this. It's always better to use an explicit conversion function (like VALUE()) to make your code clear and predictable.

Final Thoughts

While Power Query lays the foundation, mastering DAX conversion functions like VALUE, FORMAT, and DATEVALUE gives you deeper control over your data directly within the Power BI data model. This enables on-the-fly transformations inside measures and highly customized calculated columns that let you take your analysis and reports to the next level.

This level of hands-on data wrangling is essential for custom reports but highlights how much manual effort can go into traditional business intelligence. At Graphed, we created a way to eliminate this complexity. Instead of wrestling with DAX or configuring Power Query steps, you can connect your data sources and simply ask in plain English: "Show me a comparison of revenue vs ad spend for campaigns this quarter." We handle the data types and backend conversions automatically, turning your questions into interactive, real-time dashboards in seconds, not hours.