Instructor: Thomas Festus (LinkedIn)

Microsoft's Power BI is a service for business analytics that offers interactive visualizations and business intelligence features with a user interface that is easy enough for end users to utilize to construct their own reports and dashboards. In order to gain insights and make data-driven decisions, it enables users to connect to many data sources, process and manipulate data, and generate visualizations.

Power BI uses the mathematical language DAX (Data Analysis Expressions) for data modeling and computations. Users can create sophisticated computations, aggregate data, and develop custom calculations based on business logic using this robust language.

In this data digest episode, I will walk you through the following highlighted below:

1. Introduction to DAX in Microsoft BI (DAX functions and Operators)
2. DAX References to Model Objects
3. DAX variables
4. Comments in DAX
5. Using DAX with Power BI Sample Data
6. Create a new table from an existing table using DAX (Calculated Table)
7. Creating calculated measures with DAX
8. Comment in DAX formula bar
9. Creating and using variable with DAX

Let’s get started…

Introduction to DAX in Microsoft BI

DAX expressions can be used to create measures, calculated columns, and calculated tables in Power BI. Measures are calculations based on an aggregation function, such as SUM, AVERAGE, MIN, or MAX. Calculated columns are created by using a DAX expression to generate a new column based on existing data. Calculated tables are created by using a DAX expression to generate a new table based on existing data.

DAX functions

DAX is a functional language that is used to create calculations, which can be combined with logical and conditional operators to create more complex calculations. Some of the commonly used DAX functions in Power BI include aggregate functions, date and time functions, statistical functions, text functions, financial functions, time intelligence functions, and table functions.

DAX Variables

In DAX, variables are used to temporarily store and manipulate values or expressions. A variable is defined using the VAR keyword, followed by a variable name, an equal sign, and an expression. You can create variables within an expression by using <VAR>

Comments in DAX

Comments makes expression easier to understand and guides other users. In DAX, comments can be added to explain the purpose or logic of a formula or expression. A comment starts with two forward slashes (//) and everything after that on the same line is ignored by DAX. You can also use /* comment */ for multi-line comment.

Using DAX with Power BI Sample Data

• For this project, I will be working with power BI sample dataset.

• Click on try sample data and this pop-ups options below, select the load sample data

• This pops-up a navigator, select the financial data which has already been transformed and load into Power BI

• Click on the Data view on the side and get to see the data to be used.

Create a new table from an existing table using DAX (Calculated Table)

• First, go to Table Tools tab, then select the New Table - this allows you to write a DAX formula to create a calculated table.

• This table is to get the unique element in product column of financial data. First, create the name of the table “PRODUCTS”

• This creates the table with the unique or distinct products from the parent data (financials). The resulting table is an example of a calculated table.

Creating calculated measures with DAX

To create a measure that sums up the unit of good sold column in the financial data.

• Go to the report section, select new measure

• This pops out the formula bar where one can input DAX formula to create the measure.

Note: The symbol that represent the new measure is different from that of a calculated table and column. And also, the value generated from the created measure will not be physically shown but can be used in the report creation. Measures are very dynamic and powerful; this is because measures work in a filter context.

• Create a card with the calculated measure (orders) to see it in the report section. Select the orders, then card as type of visualization. This indicates that our total orders is 1.13 million.

• The calculated measure can be used to create a basis of comparison with other features (columns)
• Let’s create orders by country table, which shows the unit sold per country. Select the country and order measure from the field and then select the table from the data visualization type.

• Then select the bar chart as the visualization type and sort by orders in a descending order. This would look like this.

• This can also be used to create a column chart for orders by segment. From the financial table, select the segment column and the calculated order measure.

Comment in DAX formula bar

• Go to the formula bar and press shift enter in order to enter a single line comment that describe the measure to be created. This is done by using two forward slash (//)

• To create a comment that has multiple lines, you will use the (/*) to start the comment on the first line and (*\) to end it on the last line of the comment.

Creating and using variable with DAX

• Go to the formula bar and create the variable you want to use by starting with VAR and then press “RETURN”. Then you can use the variable to create DAX

Note: It is not logical to add age and height, but this was used just for example purposes. Also, you can use other operators like multiplication, subtraction, division and so on the expression based on your requirement for the report.

• This would be shown in the field section “VARIABLES” and the result can be used to create a card view that complement the report that is been developed.

Conclusion

Power BI's DAX is a potent tool for data analysis and modeling, and it can be utilized to build custom calculations that are suited to particular business needs.

The DAX (Data Analysis Expressions) language in Power BI is a powerful tool for performing calculations and creating measures and calculated columns. With DAX, you can create complex formulas that can analyze large amounts of data and provide insights into your data for viable decision making.

If you want to explore more on building your Microsoft Power BI skill, visit our learning path HERE

Also, if you want to get started with data analytics and looking to improving your skills, you can check out our Learning Track