In the Novem­ber 2023 fea­ture release, a fourth view, the DAX Query View, was intro­duced to Power BI Desktop. This new fea­ture is designed for Power BI busi­ness users, enabling them to write and run DAX quer­ies nat­ively within Power BI. Pre­vi­ously, execut­ing DAX quer­ies was only pos­sible using a third-party external tool or an API.

Model authors can quickly val­id­ate data and meas­ures when there is no data ware­house avail­able and without need­ing to cre­ate a visual, pub­lish, or use an addi­tional tool. What’s more, it’s pos­sible to cre­ate and update semantic model meas­ures dir­ectly from the DAX Query View.

It’s import­ant to note that DAX quer­ies dif­fer from the DAX expres­sions used to cre­ate model items such as meas­ures and cal­cu­lated columns. The res­ult is always tab­u­lar, not sin­gu­lar, sim­ilar to SQL quer­ies that return data in a table format.

With this first release, the DAX Query View is only avail­able in Power BI Desktop, although there are plans to extend DAX quer­ies to live-con­nec­ted reports and the Power BI service.

Below you can see the lay­out of the DAX Query View:

DAX query tabs are kept when the report is saved, so every time the .pbix file is opened, the quer­ies are access­ible in the DAX Query View. Each tab can be renamed for the easy iden­ti­fic­a­tion of indi­vidual code snippets.

Data Qual­ity

Data qual­ity is typ­ic­ally defined as the degree to which data meets the spe­cific needs of an organ­isa­tion within a par­tic­u­lar con­text, and as data is at the heart of all organ­isa­tional activ­it­ies, poor data qual­ity can lead to neg­at­ive busi­ness out­comes, such as erro­neous decisions and fin­an­cial losses.

There are six dimen­sions used to meas­ure the qual­ity of busi­ness inform­a­tion: accur­acy, com­plete­ness, con­sist­ency, valid­ity, unique­ness, and timeli­ness. Good data gov­ernance prac­tices include har­mon­ising data from vari­ous sources, cre­at­ing and mon­it­or­ing data usage policies, and elim­in­at­ing incon­sist­en­cies and inac­curacies to main­tain these qualities.

Return­ing to the Power BI fea­ture we’re look­ing at today, the DAX Query View can be used by Power BI developers to assess model data qual­ity, allow­ing users to identify issues such as duplic­ates, null val­ues, lost data, and incor­rect formats.

Example

For this example, we’ll be using the Con­toso Sales Power BI Designer file, avail­able here.

We’ll look at a scen­ario where no data ware­house is avail­able, so all raw data is impor­ted into Power BI and mod­elled using Power Query.

You can see the model below:

How to Use the DAX Query Editor

As men­tioned earlier, the DAX Query View in Power BI enables users to cre­ate DAX quer­ies, which dif­fer from DAX for­mu­las. A DAX query is like an SQL query and is used to view data in the model.

There are two main parts to a DAX query:

  • EVALUATE (required): This state­ment is neces­sary to spe­cify the table expres­sion that con­tains the data to be viewed. A DAX query can con­tain mul­tiple EVALUATE statements.
  • DEFINE (optional): This state­ment is used to spe­cify meas­ures and vari­able defin­i­tions that enhance the func­tion­al­ity of the EVALUATE statements.

Inside the DAX Query View, users can define or modify meas­ures that already exist in the model. These changes are tem­por­ary and only affect that spe­cific DAX query ses­sion, so users can exper­i­ment with modi­fic­a­tions. There is also an option to per­man­ently update the meas­ures in the model based on the changes made dur­ing the session.

Another Power BI fea­ture called Quick Quer­ies helps users to start their quer­ies more effi­ciently. For instance, to view the top 100 rows of the Sales table, simply right-click on Sales in the data pane, then select Quick Quer­ies > Show top 100 rows:

With this quick query, you can eas­ily exclude (or com­ment out) unne­ces­sary columns from the res­ults, modify the num­ber of dis­played rows, adjust the order, and more.

Null Check

For this example, two meas­ures were cre­ated, Profit and % Profit Mar­gin; see the DAX defin­i­tion below:

To sim­u­late a scen­ario where null val­ues may occur, ima­gine there was an issue with the data load caus­ing all online stores to have IDs that do not exist in the fact table. Con­sequently, when the join is per­formed, some stores will appear with null dimen­sions because their IDs can­not be found:

As can be seen above, the blank val­ues are in the Geo­graphy table. The tables are joined using the StoreKey column, so the focus for find­ing a solu­tion should be on check­ing the val­ues in this column:

The first few rows dis­play the val­ues that are caus­ing blanks in the Con­tin­ent­Name column. Since the expec­ted val­ues are greater than 0, as they appear in the fact table, the online stores can­not be asso­ci­ated with any continent.

Duplic­ates Check

In the Con­toso data model, the Sales table is the fact table around which the model is built. It is pos­sible to check duplic­ates using the DAX Query Editor like in a SQL-based database.

In this fact table, each sale is iden­ti­fied by a unique value, named Sales­Key, so this column will be used as the primary key in the example. Below is a sample of the code that could be used:

In the fig­ure above, the COUNT column shows the num­ber of rows for each Sales­Key, while the IsDu­plic­ate column indic­ates whether a row is duplic­ated. Duplic­ates can be detec­ted if the COUNT column exceeds 1 or if the IsDu­plic­ate value is 1.

A fil­ter has been applied to dis­play only the duplic­ated rows, those for which IsDu­plic­ate is equal to 1.

Below you can see the query results:

As there are no rows in the res­ults grid, the model has no duplicates.

Ima­gine that the pro­cess to assign the Sales­Key fails, res­ult­ing in two rows receiv­ing the value 1. If the same query is run under these con­di­tions, the res­ults would appear as shown below:

The query shows the value of each row to address duplic­ates in the fact table.

Format Check

Another import­ant aspect to check is whether val­ues are in the expec­ted format; the DAX Query View can also be used for this pur­pose. For example, to verify that each per­son has been assigned an email, an ‘@’ should appear in every row of the Email column.

In the example data­set, there is no such column, so the focus will shift to veri­fy­ing if there are any incor­rect dates lis­ted as end dates for pro­mo­tions. Below is an example of the pro­mo­tion table:

The default value for the column is 01/01/9999, so if the user doesn’t modify it or if there is an error while load­ing it, the dis­count will remain indef­in­itely valid.

Con­sequently, it is essen­tial to verify whether there are any incor­rect dates:

The res­ults are as follows:

Pro­mo­tion 14, related to the Asian hol­i­days, is not well defined as it seems to never end.

Test Schema

A great way to auto­mate data qual­ity in the report is with a stand­ard test struc­ture for each item, as sug­ges­ted by Anelle Ker­ski in her DataOps series. Since DAX query tabs are saved with the model, it is pos­sible to build a script that encap­su­lates all the tests needed to check the model every time there’s a change.

Let’s con­sider a scen­ario for our model where val­id­a­tion is pos­sible based on the fol­low­ing points, not­ing that the tests relate to the aspects dis­cussed above:

  • The num­ber of rows con­tain­ing dis­tinct primary keys in the fact table should match the total num­ber of rows.
  • The End­Date range for the pro­mo­tions should be coherent.
  • The query used for the Null Check above should not return null values.

The first step is to set the fil­ter defin­i­tions to snap­shot the res­ults as the data grows; these fil­ters must be static. In our case, we are only con­sid­er­ing data from 2011 to 2014, as this is the only period when the fact table is val­id­ated, so it is the only one that needs to be checked when a trans­form­a­tion is car­ried out, to ensure that res­ults that were cor­rect in the past are not altered:

Next, we need to declare the vari­ables that con­tain the expec­ted and actual val­ues of the three tests:

Then we cre­ate a table that dis­plays the res­ults using the union function:

Once all the code has been executed, the fol­low­ing res­ults will be shown:

The first column provides a descrip­tion of the test per­formed, the second shows the expec­ted res­ult, and the third dis­plays the res­ult obtained with the cur­rent model; the last column indic­ates whether the test was passed, mak­ing it easier to read.

From the res­ults, it is evid­ent that the model requires fur­ther inspec­tion, as 2 out of 3 tests failed.

The advant­age of employ­ing a file like the one described is that it is access­ible to both tech­nical and non-tech­nical users. What’s more, this stream­lined pro­cess means a quicker veri­fic­a­tion of each item, redu­cing the time required to imple­ment solutions.

Con­clu­sions

The DAX Query View is a power­ful new fea­ture that enables Power BI developers to stream­line data qual­ity checks, elim­in­at­ing reli­ance on third-party tools. Although DAX quer­ies dif­fer from DAX expres­sions, developers famil­iar with expres­sions will not face a sig­ni­fic­ant tech­nical chal­lenge in adapt­ing to DAX quer­ies. Power BI also enhances usab­il­ity by auto­mat­ic­ally sav­ing each query tab every time a report is saved, ensur­ing that tests can be rep­lic­ated as needed.

If you have any ques­tions about this art­icle or need fur­ther assist­ance with Power BI and DAX quer­ies, please do not hes­it­ate to con­tact us. Our exper­i­enced and cer­ti­fied team of experts are here to help you stream­line your data ana­lysis and to optim­ise your report­ing capabilities!