What is DAX and How to Load External Data into Power BI

Published on June 18, 2026 · 9 min read

Power BI is one of the most powerful business intelligence tools on the market, and at its heart lies DAX — Data Analysis Expressions. Whether you're building interactive dashboards, creating complex calculations, or loading external data directly into your reports, understanding DAX is essential for getting the most out of Power BI.

In this guide, we'll cover what DAX is, how it works, the difference between measures and calculated columns, and — most importantly — how to load external data from CSV and Excel files into Power BI using DAX expressions like DATATABLE.

What is DAX?

DAX (Data Analysis Expressions) is a formula language developed by Microsoft for Power BI, Power Pivot, and Analysis Services. Think of it as a supercharged version of Excel formulas, designed specifically for working with relational data models and performing sophisticated analytical calculations.

DAX allows you to:

• Create calculated columns that add computed values to your tables
• Define measures that perform dynamic aggregations based on report context
• Build calculated tables entirely from expressions
• Implement complex time intelligence functions (year-over-year, moving averages, etc.)
• Control filter context to create advanced business logic

Here's a simple DAX measure that calculates total revenue:

Total Revenue = SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

While this looks similar to an Excel formula, DAX operates on entire tables and columns rather than individual cells. This columnar approach is what makes it so powerful for large-scale data analysis.

DAX Measures vs. Calculated Columns

One of the most important concepts to grasp early on is the difference between measures and calculated columns. Choosing the wrong one is the most common mistake Power BI beginners make.

Calculated Column Example

A calculated column adds a new column to an existing table. It's evaluated once per row during data refresh:

Profit Margin = 
DIVIDE(
    Products[Revenue] - Products[Cost],
    Products[Revenue],
    0
)

This creates a new column in the Products table with the profit margin for each product.

Measure Example

A measure dynamically recalculates based on the current filter context — for example, when a user selects a specific region in a slicer:

YTD Revenue = TOTALYTD(
    SUM(Sales[Amount]),
    Calendar[Date]
)

This measure automatically adjusts its result depending on which filters are active in the report.

The DATATABLE Function: Creating Tables in DAX

One of DAX's most useful but underutilized features is the DATATABLE function, which lets you define a table directly within a DAX expression. This is incredibly handy when you need to embed reference data, lookup tables, or test datasets directly into your Power BI model without importing external files.

Here's the syntax:

DATATABLE(
    "ColumnName1", DataType,
    "ColumnName2", DataType,
    ...,
    {
        { Value1, Value2, ... },
        { Value1, Value2, ... }
    }
)

Supported data types include INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, and DATETIME.

Practical DATATABLE Example

Let's say you need a region mapping table for your sales dashboard:

RegionMapping = DATATABLE(
    "RegionCode", STRING,
    "RegionName", STRING,
    "Territory", STRING,
    "TargetRevenue", CURRENCY,
    {
        { "NA-E", "North America East", "Americas", 2500000 },
        { "NA-W", "North America West", "Americas", 3100000 },
        { "EU-N", "Europe North", "EMEA", 1800000 },
        { "EU-S", "Europe South", "EMEA", 1400000 },
        { "APAC", "Asia Pacific", "APAC", 2200000 }
    }
)

This creates a fully functional table in your model that you can use in relationships, calculated columns, and measures — no CSV import required.

Converting CSV and Excel Data to DAX Expressions

While DATATABLE is powerful, manually typing out hundreds of rows is impractical. This is where automated conversion becomes essential. The typical workflow looks like this:

1. Start with your data in a CSV or Excel file
2. Convert it to a DAX DATATABLE expression
3. Paste the expression into Power BI Desktop as a new calculated table
4. Build relationships and start analyzing

Here's what a converted CSV might look like as a DAX expression:

Original CSV:

product_id,product_name,category,unit_price
SKU-001,Wireless Mouse,Peripherals,29.99
SKU-002,Mechanical Keyboard,Peripherals,89.99
SKU-003,USB-C Hub,Accessories,45.00
SKU-004,Monitor Stand,Furniture,119.99
SKU-005,Webcam HD,Peripherals,59.99

Converted DAX DATATABLE:

Products = DATATABLE(
    "product_id", STRING,
    "product_name", STRING,
    "category", STRING,
    "unit_price", DOUBLE,
    {
        { "SKU-001", "Wireless Mouse", "Peripherals", 29.99 },
        { "SKU-002", "Mechanical Keyboard", "Peripherals", 89.99 },
        { "SKU-003", "USB-C Hub", "Accessories", 45.00 },
        { "SKU-004", "Monitor Stand", "Furniture", 119.99 },
        { "SKU-005", "Webcam HD", "Peripherals", 59.99 }
    }
)

Doing this conversion by hand for large datasets is tedious and error-prone — you need to correctly map data types, escape strings, format numbers, and ensure every comma and bracket is in the right place. ConvertMatrix's CSV to DAX Converter and Excel to DAX Converter handle all of this automatically, generating clean, paste-ready DAX expressions in seconds.

When to Use DATATABLE vs. Power Query Import

Power BI offers multiple ways to bring data into your model. Here's when each approach makes sense:

The DATATABLE approach shines when you need a self-contained .pbix file — for example, when sharing a template or demo report that shouldn't depend on external file paths. The data lives inside the DAX expression itself, making the report fully portable.

Essential DAX Functions Every Power BI User Should Know

Beyond DATATABLE, here are the DAX functions that form the backbone of most Power BI reports:

Aggregation Functions

// Basic aggregations
Total Sales = SUM(Sales[Amount])
Average Order = AVERAGE(Sales[OrderTotal])
Order Count = COUNTROWS(Sales)

// Conditional aggregation
High Value Orders = CALCULATE(
    COUNTROWS(Sales),
    Sales[OrderTotal] > 500
)

Time Intelligence Functions

// Year-to-date
YTD Sales = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

// Same period last year
SPLY Sales = CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Calendar[Date])
)

// Month-over-month growth
MoM Growth = 
VAR CurrentMonth = SUM(Sales[Amount])
VAR PreviousMonth = CALCULATE(
    SUM(Sales[Amount]),
    DATEADD(Calendar[Date], -1, MONTH)
)
RETURN DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth, 0)

Filter Manipulation

// Remove filters on a specific column
All Products Revenue = CALCULATE(
    SUM(Sales[Amount]),
    ALL(Products[Category])
)

// Percentage of total
Category Share = DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Products[Category])),
    0
)

Power BI Tips for Working with DAX

After working with DAX across hundreds of Power BI projects, here are the tips that make the biggest difference:

• Use variables (VAR). They improve readability, prevent redundant calculations, and make debugging easier. Always prefer VAR/RETURN over repeating the same sub-expression.
• Format your DAX code. Use tools like DAX Formatter (daxformatter.com) to automatically indent and structure complex expressions. Readable code is maintainable code.
• Prefer measures over calculated columns when doing aggregations. Measures use less memory and respond to filter context dynamically.
• Create a dedicated date table. Time intelligence functions require a contiguous date table marked as a date table in the model. Use CALENDAR or CALENDARAUTO to generate one.
• Avoid circular dependencies. A calculated column cannot reference a measure that depends on the same table. Plan your calculation layers carefully.
• Use DIVIDE instead of /. The DIVIDE function handles division by zero gracefully without throwing errors.

Embedding External Data: A Step-by-Step Workflow

Here's a complete workflow for getting your CSV or Excel data into Power BI as a DAX table:

1. Prepare your data: Clean up your CSV or Excel file. Ensure consistent column names, no merged cells, and proper data types.
2. Convert to DAX: Use ConvertMatrix's CSV to DAX tool or Excel to DAX tool to generate the DATATABLE expression. The converter automatically detects column types and formats the output.
3. Open Power BI Desktop: Go to Modeling → New Table in the ribbon.
4. Paste the DAX expression: Replace the placeholder text in the formula bar with your generated DATATABLE expression.
5. Verify the table: Check the Data view to ensure all rows and columns imported correctly.
6. Create relationships: Link your new table to existing tables in the model using the Relationships view.
7. Build visuals: Start dragging columns and measures onto your report canvas.

Conclusion

DAX is the engine that powers every calculation, aggregation, and analytical insight in Power BI. From simple sums to complex time intelligence and filter manipulation, mastering DAX transforms you from a report viewer into a report builder.

The DATATABLE function is particularly valuable for embedding portable reference data directly into your models — and when combined with automated conversion tools, it becomes a fast, practical workflow for loading external data.

Ready to convert your data into Power BI-ready DAX expressions? Try these free tools on ConvertMatrix:

• CSV to DAX Converter — Transform CSV files into paste-ready DATATABLE expressions
• Excel to DAX Converter — Convert Excel spreadsheets to DAX with automatic type detection

All conversions run 100% in your browser — your data never leaves your machine. No sign-up, no uploads, no limits.