Power BI is fantastic for building reports, but sometimes you just need to get data out of a semantic model quickly, without building a full report page to do it.

However, what if you need to dig further into a specific snapshot of your data?

For example:

What if you wanted to pull a precise slice of data into Excel for a one-off analysis? Or maybe you’re building a bespoke report and want to make sure it’s using the same agreed business logic as everyone else, without duplicating measures from scratch.
This is where querying your semantic model with DAX comes in.

DAX queries allow you to return data directly from a semantic model, much like SQL returns data from a database. There are some syntactic and structural differences between the two, but the concept is familiar. Write a query, get a table back.
There are multiple ways to write and run DAX queries against a semantic model:

  • Within Fabric. A natural option if you are already working within the Fabric ecosystem. You can query your semantic model directly.
  • Power BI Desktop. The most familiar starting point for most developers.
  • SQL Server Management Studio. A solid option if you’re comfortable working outside of Power BI.
  • DAX Studio. A dedicated query tool with a drag-and-drop builder, full DAX editor, and excellent performance diagnostics. Great for optimising slow measures before they reach your reports.
  • Power Query. The less obvious option, but very powerful if you want to load a DAX query result as a table directly into Excel or Power BI.

Example Scenario

The finance team within your business are SQL savvy and usually build their own queries directly against your Data Warehouse. However, their figures rarely match the wider business and their SQL queries have evolved over the years to the point where nobody really understands what they do anymore.

What if there was a way for them to keep writing queries in a familiar environment, whilst ensuring they are using the same logic as everyone else?

This is where querying your semantic model with DAX comes in.

Why DAX Instead of SQL?

Here’s where DAX against the semantic model has some real advantages.

Your measures come with you.

The semantic model already contains built-in measures with agreed business logic baked in, things like margins, totals, and conversion rates. When you query using DAX, you get those for free rather than having to recreate the logic yourself.

No joins needed.

DAX uses the relationships already defined in the semantic model. Any dimension connected to your fact tables, dates, products, customers, is available directly without manually writing joins to bring them together.

Bespoke reporting without reinventing the wheel.

If you need a custom table for a one-off extract, you can write a DAX query inside Power Query to pull exactly what you need, shaped how you want it, whilst still using the logic within the semantic model.

DAX vs SQL

DAX queries look similar to SQL on the surface but work quite differently under the hood. Where SQL requires you to explicitly join tables and define relationships in the query itself, DAX works through the relationships already set up in the model, so those joins happen automatically. This results in simpler queries, a lot of the time having far fewer lines.

Structurally, a DAX query uses EVALUATE with table expressions rather than SELECT and FROM.

Tip: If you’re new to DAX queries, DAX Studio is the best place to start. The drag-and-drop builder lets you construct queries without knowing all the syntax upfront, and you can see exactly how long each query takes to run.

Comparing Queries

Both the below queries return the same result, monthly sales and cost figures by region for 2019, ordered by month and total sales.

The SQL query needs to explicitly define three tables and join them together using their respective keys before it can even get to the data.

The DAX query, on the other hand, simply references the columns and measures it needs. The relationships between the tables are already defined in the semantic model, so there is no need to spell them out.

The measures [Total Cost] and [Total Sales] also bring their own logic with them, meaning the DAX query is not just shorter, it is also guaranteed to return figures consistent with every other report in the business.

Wrapping Up

Hopefully this blog has shown that DAX is not as daunting as it might first appear, especially if you are already comfortable writing SQL.

For teams like finance who are used to querying data directly, DAX queries offer a familiar way of working whilst ensuring everyone is pulling from the same logic.

No more mismatched figures, no more queries that have grown so complex nobody knows what they do anymore.

If you have a semantic model already built and maintained in your business, it makes sense to get as much value out of it as possible. Querying it directly with DAX is a great place to start.

Tags: , , , , , ,

« Previous Post
Next Post »