DAX Query View (Native creation)

Introduction

In the Power BI ecosystem, DAX (Data Analysis Expressions) queries play a critical role by allowing BI developers to interact with their data models in advanced ways.

With the new functionality that is introduced, this barrier has been removed, allowing BI professionals to write and execute DAX queries natively within Power BI.

By Vicente Antonio Juan Magallanes -

30th November 2023 - fp20 analytics

Advantages and benefits of use

• Native Integration: The ability to create and execute DAX queries directly within Power BI offers a native integration. This simplifies the workflow and improves operational efficiency.

• Enhanced Productivity: By providing BI developers with the ability to interact with DAX queries within the same development environment, the time and steps needed to perform data analysis and modeling are reduced, resulting in increased productivity.

• Quick Access to Sample Data: Quick queries from the context menu allow for a preliminary preview of the data or summary statistics without creating visualizations.

• Time Savings in Model Updates: Time-saving options such as updating the model when modifying definitions or adding measures eliminate the need for manual processes, improving efficiency in data model management.

• Complete Context Visualization: The ability to define measures and their references, showing all DAX expressions on one screen, provides a complete view of the calculation context. This facilitates understanding of how a measure is calculated in relation to the source columns.

• Ease of Creation and Maintenance: The ability to add new measures to the model directly from the DAX queries, along with time-saving options, simplifies the creation and maintenance of measures, reducing the complexity of the process.

Let's activate this preliminary feature!

First, we must activate the latest update:
Navigate to the ribbon and select the option called File.

Then in the dialog window, we select Options and settings.

After that, we choose Options.

Then we go to the Preview Features section.

We activate the option with the checkmark.

Let's get to work!!!

First:

On the left side of our Power BI Desktop, we have a new option called DAX query view.

After clicking, it takes us to a window where we can create our DAX queries for tables, columns, or measures.

Second:

We must know the keywords we can use, the first one we will see is EVALUATE, which is necessary.

Syntax: EVALUATE <table>

Description: Fundamental instruction that evaluates a table expression. To see the result of this instruction, we click on the RUN button.

Result: We can observe the evaluated table.

Third:

The next keyword or reserved word will be ORDER BY (optional).

Syntax: ORDER BY {<expression> [{ASC | DESC}]}[, …]

Description: Allows sorting the query results based on one or more expressions.

Result: We can observe the evaluated and sorted table.

Fourth:

The next keyword or reserved word will be START AT (optional).

Syntax: START AT {<value>|<parameter>} [, …]

Description: Used within the ORDER BY clause to define the initial value for the query results.

Result: We can observe the evaluated and sorted table, starting with the value specified in the query.

Fourth:

The next keyword or reserved word will be DEFINE.

DEFINE is a keyword that precedes the definitions of measures, variables, tables, or columns. In this case, you are defining a measure.

MEASURE 'Fact Crime Person'[Total People Involved] defines a new measure called 'Fact Crime Person'[Total People Involved] in the 'Fact Crime Person' table.

EVALUATE is another keyword that initiates an instruction that returns a dataset. Here, you are evaluating the expression {'Fact Crime Person'[Total People Involved]}, which displays the result of the measure we just defined.

After creating our measure, we can add it to our data model, as this option is available.

It provides a confirmation of the action or event, which we then confirm.

On the right-hand side where our tables are located, we can observe our new measure.

1. View Top 100 Rows: Provides a quick preview of the data, which is useful for identifying patterns, errors, or simply exploring information in a small sample.

2. Column Statistics: Offers statistical summaries of the columns, such as average, minimum, maximum, standard deviation, etc. It helps understand the distribution and characteristics of the data.

3. Define All Measures for the Table: Allows creating custom measures in the context of a specific table.

4. Define All Measures for the Model: Makes it easier to create measures that can span multiple tables and provide a comprehensive view of data model metrics.

Using DAX queries in Power BI provides BI professionals and analysts with a versatile and efficient tool for exploring, analyzing, and customizing data. The flexibility to visualize the top 100 rows, examine column statistics, and define measures at the table or model level allows for a customized approach based on analysis needs. This capability, combined with productivity-enhancing features like auto-completion and quick queries, contributes to more agile development and informed decision-making in business intelligence projects. Together, DAX queries enhance the effectiveness of data analysis in Power BI, providing a robust framework for exploring and understanding complex datasets.

Conclusion: