Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Replace Values in Power BI Using DAX

Cody Schneider••8 min read

Working with real-world data often means dealing with inconsistencies. You might have customer data with different spellings of the same country, product codes with unnecessary prefixes, or log files with cryptic status numbers you need to translate into something human-readable. This article will walk you through several easy-to-use DAX functions in Power BI - primarily SWITCH, REPLACE, and SUBSTITUTE - to clean and standardize your values, making your reports clearer and more accurate.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Why Do You Need to Replace Values?

Before jumping into the DAX formulas, it's helpful to recognize the common situations where replacing values is a game-changer for your reports. Cleaning your data at the source is always ideal, but that's not always possible. That's where DAX comes in.

Here are a few frequent scenarios:

  • Standardizing Inconsistent Data: Your data might contain variations like "USA," "U.S.A.," and "United States." For accurate analysis, you need to consolidate them into a single, standard value like "United States."
  • Improving Readability: Your database may use codes like "1" for "Active," "2" for "Pending," and "3" for "Completed." Replacing these numbers with their descriptive text labels makes your charts and tables instantly understandable for your audience.
  • Categorizing or Grouping Data: You might want to group specific values into broader categories. For example, you could replace individual cities like "London," "Paris," and "Berlin" with a broader "Europe" category for high-level regional analysis.
  • Correcting Errors or Typos: Sometimes, the data is just plain wrong. A product might be misspelled or a category mislabeled. DAX lets you fix these issues directly within your report.

Method 1: The SWITCH Function for Conditional Replacement

The SWITCH function is your go-to tool for replacing an entire value with another based on a set of conditions. Think of it as a cleaner, easier-to-read version of nested IF statements. It checks an expression against a list of values and returns a corresponding result when it finds a match.

Understanding the SWITCH Syntax

The basic structure of the SWITCH function looks like this:

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., [<else>])
  • &lt,expression&gt,: The column or value you want to evaluate.
  • &lt,value1&gt,, &lt,result1&gt,: The first pair. If &lt,expression&gt, equals &lt,value1&gt,, the function returns &lt,result1&gt,.
  • [...]: You can add as many value/result pairs as you need.
  • [&lt,else&gt,]: An optional value to return if the expression doesn't match any of the specified values. This is crucial for avoiding errors or unintended blank results.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Example: Cleaning Up Sales Channel Names

Imagine you have a sales table with a column called 'Channel' containing values like "Web", "Store", and "Phone". You want to create a new, more descriptive column called 'Sales Channel Name' with the values "E-commerce", "In-Store Retail", and "Direct Sales".

Step 1: Create a Calculated Column

In the Data view of Power BI, select your sales table. Right-click anywhere in the table view or go to the "Table tools" ribbon and click New column.

Step 2: Write Your SWITCH Formula

In the formula bar, name your column and enter the following DAX formula:

Sales Channel Name =
SWITCH (
    'Sales'[Channel],
    "Web", "E-commerce",
    "Store", "In-Store Retail",
    "Phone", "Direct Sales",
    'Sales'[Channel]
)

Step 3: Analyze the Formula

  • 'Sales'[Channel] is the expression we are evaluating for each row.
  • "Web", "E-commerce" is our first value/result pair. If a row's 'Channel' is "Web", the new column will display "E-commerce".
  • The formula repeats this logic for "Store" and "Phone".
  • The final 'Sales'[Channel] acts as the else condition. If the channel is something other than "Web", "Store", or "Phone", this tells Power BI to just keep the original value. Without this safety net, any unmatched channels would result in a blank entry.

Advanced Tip: Using SWITCH(TRUE()) for Complex Conditions

You can also use a powerful pattern, SWITCH(TRUE(), ...), to evaluate logical conditions instead of just looking for exact matches. This works great for grouping numerical data.

Let's say you want to categorize sales transactions into "Small", "Medium", or "Large" based on the transaction amount.

Transaction Size =
SWITCH (
    TRUE(),
    'Sales'[TransactionAmount] < 100, "Small",
    'Sales'[TransactionAmount] < 500, "Medium",
    'Sales'[TransactionAmount] >= 500, "Large",
    "Other"
)

Here, Power BI evaluates each logical condition from top to bottom. As soon as it finds one that is TRUE, it returns the corresponding result and stops, allowing for sophisticated bucketing and categorization.

Method 2: The REPLACE Function for Position-Based Changes

What if you don't want to replace the entire value, but just a part of it? The REPLACE function is designed for changing a specific portion of a text string based on its starting position and length.

Understanding the REPLACE Syntax

REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
  • &lt,old_text&gt,: The original text or column containing the text.
  • &lt,start_num&gt,: The character position where you want to start replacing text (the first character is 1).
  • &lt,num_chars&gt,: The number of characters you want to replace.
  • &lt,new_text&gt,: The new text string to insert.
GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Example: Removing a Prefix from Product IDs

Suppose you have a 'ProductID' column where every entry starts with "PID-". For example, "PID-10255", "PID-10256", etc. You want a column that just shows the numerical part.

Step 1: Create a new Calculated Column for the cleaned IDs.

Step 2: Write the REPLACE Formula

Clean ProductID =
REPLACE (
    'Products'[ProductID],
    1,
    4,
    ""
)

This formula tells Power BI to look at the 'ProductID' column, start at the very first character, replace 4 characters ("PID-"), and insert an empty text string ("") in their place - effectively deleting the prefix.

Method 3: The SUBSTITUTE Function for Finding and Replacing Text

The REPLACE function works beautifully when you know the exact position of the text you're changing. But what if the text you need to replace could appear anywhere in the string? For that, SUBSTITUTE is the perfect tool.

Understanding the SUBSTITUTE Syntax

SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])
  • &lt,text&gt,: The original text field or column.
  • &lt,old_text&gt,: The specific piece of text you want to find and replace.
  • &lt,new_text&gt,: The new text to replace it with.
  • [&lt,instance_num&gt,]: An optional parameter. If you only want to replace a specific occurrence of the text (e.g., only the second time it appears), you can specify that here. If you omit it, it replaces all occurrences.

Example: Standardizing URL Formats

Let's say a 'Website' column contains URLs, but some use "http://" while you want everything to be standardized to "https://".

Step 1: Create a new Calculated Column for "Standard URL".

Step 2: Write the SUBSTITUTE Formula

Standard URL =
SUBSTITUTE (
    'Websites'[URL],
    "http://",
    "https://",
    1
)

This formula finds the text "http://" within each URL and replaces it with "https://". Using an instance number of 1 ensures only the first occurrence is replaced, which is good practice for URL prefixes.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Best Practice: Replacing Values with a Related Table

While DAX functions offer quick fixes, the most robust and maintainable method for replacing keys or codes is to use Power BI's data model. Instead of hard-coding replacements in a giant SWITCH statement, you can create a lookup table (also called a dimension table).

For instance, if your sales data has CountryID codes (1, 2, 3) instead of names, you could write a SWITCH function: SWITCH('Sales'[CountryID], 1, "USA", 2, "Canada", ... ). But what happens when you add a new country? You have to edit the formula.

A much better way is to:

  1. Create a separate table named 'Countries' with two columns: CountryID and CountryName.
  2. In the Model view of Power BI, create a relationship between 'Sales'[CountryID] and 'Countries'[CountryID].
  3. Now, you can add a new column to your Sales table using the RELATED function:
Country = RELATED('Countries'[CountryName])

This approach is easier to update (you just add a row to your 'Countries' table) and aligns with data modeling best practices, making your reports scalable and easier to manage long-term.

Final Thoughts

Mastering value replacement in Power BI is a fundamental skill for anyone serious about creating clean, reliable, and user-friendly reports. Whether you’re using SWITCH for broad conditional swaps, REPLACE for position-based edits, SUBSTITUTE for specific text changes, or leaning on your data model with RELATED, these techniques give you the power to transform raw data into clear, actionable information.

While learning DAX is incredibly powerful, it's true that for many marketers and business owners, it represents another BI tool with a steep learning curve. The process of connecting data, cleaning it, and then writing precisely the right formulas to get the dashboard you need can still take hours. At Graphed, we remove that friction. We believe you should be able to get answers from your data by just asking questions in plain English. Simply connect your various marketing and sales data sources, and you can generate entire dashboards by typing things like, "show me a dashboard of my Shopify sales vs. Facebook Ads spend for the last quarter." We built it to automate the repetitive reporting work so you can skip straight to the insights.

Related Articles