Creating a Custom Column with a Random String in Power BI Using DAX

Introduction

In Power BI, customizing your dataset by adding calculated columns can significantly enhance your data analysis capabilities. One common need is to generate random strings or categories for testing purposes, simulating scenarios, or assigning values like branch names, IDs, or categories to your data. In this post, we’ll explore how to create a custom column with a random string using DAX (Data Analysis Expressions) in Power BI.

Goal

We will create a column that generates a random string for each row of your data. The string could be a random category like branch names, product codes, or user roles, all of which are assigned dynamically using a custom formula.

Steps to Create a Custom Column with Random Strings in Power BI

Step 1: Open the Data View

Start by opening your Power BI project and switching to the Data view by clicking on the table icon on the left. This is where you will be able to manage and modify the data in your tables.

Step 2: Select the Table

Next, select the table in which you want to create the custom column. We will add a new calculated column that generates random strings based on your specific needs.

Step 3: Create a New Column

In the ribbon, click on New Column to create a new calculated column. This will open the formula bar where you can input the DAX expression to generate random strings.

Step 4: Use the DAX Expression for Random Strings

In the formula bar, enter the following DAX code to create a custom column with random strings. For this example, we’ll generate random branch names prefixed with "BRANCH_".

CustomString = 
"BRANCH_" & 
SWITCH(
    MOD(ROUND(RAND() * 1000, 0), 5),
    0, "North",
    1, "South",
    2, "East",
    3, "West",
    4, "Central"
)

Explanation:

• "BRANCH_" &: Concatenates the prefix "BRANCH_" with the random branch name.
• RAND() generates a random number between 0 and 1.
• ROUND(RAND() * 1000, 0) multiplies the random number by 1000 and rounds it to an integer. This provides a random number between 0 and 1000.
• MOD(ROUND(RAND() * 1000, 0), 5) calculates the remainder when dividing the random number by 5, resulting in a value between 0 and 4.
• SWITCH: Based on the modulo result, this function assigns one of the five strings ("North", "South", "East", "West", "Central").

Step 5: Customize the Strings

You can replace the branch names with any strings that suit your needs. For example, you could generate random product codes, user roles, or any other categories. Here’s how the code would look for random user roles:

CustomString = 
"ROLE_" & 
SWITCH(
    MOD(ROUND(RAND() * 1000, 0), 4),
    0, "Admin",
    1, "Editor",
    2, "Viewer",
    3, "Guest"
)

Step 6: Verify the Output

Once you’ve entered the formula, press Enter. Your custom column will now be filled with random strings for each row in your dataset.

Example of Expected Output

Here’s an example of what your dataset might look like with the random branch names:

Or with random roles:

Use Cases

• Simulating Data for Testing: Generating random strings allows you to simulate real-world data, making it easier to build and test reports before actual data is available.
• Assigning Random Categories: You can use this method to assign random categories, branches, or roles in your dataset, which could be useful for reports that need to segment data.
• Generating Unique Identifiers: You could modify this formula to generate random codes or IDs for each row.

Conclusion

Creating custom columns with random strings in Power BI is a simple but powerful technique that can enhance your data preparation process. Whether you are simulating test data, generating random categories, or assigning identifiers, the flexibility of DAX allows you to tailor the solution to your needs.