DAX to Power BI | A complete guide to learning Power BI DAX (2023)

Do you know why DAX plays an important role in data analysis in Power BI? How does creating custom measures affect your decision making?

Well, maybe you've imported some data into Power BI desktop files and even created reports that provide valuable insights. But what if you need to analyze the growth (%) of product listings over different time periods or your company's annual growth compared to the competition? This is where Power BI DAX comes into play.

In this blog, we discuss the most useful DAX functions in Power BI, how to create metrics with Power BI desktop, and more.

DAX sin Power BI

Power BI DAX Tutorial | DAX basics in Power BI | DAX functions in Power BI

First, let's start with the definition of DAX, which I'll talk about in the next section.

DAX basics in Power BI: What is DAX?

Data Analysis Expressions (DAX) is a syntax language that includes formulas and expressions used in data manipulation. Functions, constants, and operators are used in the DAX to create expressions. In simpler terms, DAX is the enhanced version of MS Excel with advanced data manipulation and management features. It was developed by Microsoft to interface with their business intelligence and data modeling tools like PowerPivot, Power BI. Power BI is a robust analytics tool from Microsoft that offers various features and functions using DAX as the language.

Some of the main benefits of Power BI are:

• Visualization of data in real time
• Creating graphs and reports on voice commands with Cortana
• Large storage space and easy memory data recovery
• Increased security at the row level
• BI has intuitive user experience features and saves the most used trends

All of these functions are essentially performed using predefined tools and commands in the system. However, there are many times when you want to perform a certain type of function, say a certain type of data analysis, but you can't because the command doesn't exist in the system. What to do in such a case? This is where DAX comes into play.

Would you like to be part of the real-time environment of Power BI? then visit hereMicrosoft Power BI Certification Course

Meaning of DAX in Power BI

DAX is quite an important syntax, as working in DAX enhances the user experience by providing features like data visualization, data import, and data manipulation. Basic knowledge of the dashboard is sufficient for normal reporting, but DAX is required to create sophisticated and detailed reports. With multiple commands and actions that can be used under the DAX syntax, the generated reports are extremely intuitive and sophisticated.

DAX in Power BI: How does it work?

DAX works on a combination of three basic concepts:

1. Syntax
2. context
3. functions

These inputs are key to building a specific command that will get the desired results.

1. Syntax

The syntax refers to the components of the formula you are creating. Is it the language used in the formula, such as command, character, operators, target column, row or table, etc.? For example, name, parentheses, sum, table name, etc.

2. Context

The context refers to the target row included in the calculation or data retrieval formula. Context comes in two types: row context and filter context.

3. Features

Functions refer to predefined or existing commands in the system. For example Add, Add, True, False, etc.

Also read:Power BI architecture tutorial

DAX in Power BI: Calculated columns and measures

In this article, we'll focus on Power BI DAX formulas used in calculations, measures, and calculated columns.

#1. calculated column

Like other regular columns, calculated columns are those that arise as a result of calculations on two columns from different data sets. They are ideal for line-by-line calculations;

#2. calculated measure

Calculated columns are ideal for dynamic calculations because they take up no physical space. They are used when grouping data into rows for calculations.

Calculated Columns x Metrics

Calculated columns and calculated measures use DAX expressions for data manipulation. The difference is in the context of the underlying formula. With the calculated column type, the calculation is done at the row level in a given table. The calculated measure type is calculated at the cell level throughout the entire report or query.

Power BI DAX basics: types of functions in DAX

#1. added functions

MINIMUM

Gets the minimum value in a specified column.

Syntax

MIN(<Coluna>)

Example

=MIN( [Seller Margin] )

Mina

Gets the minimum value along with the logical values ​​of the aggregate functions and the text representation of the numbers, if applicable

Syntax

HAND( <Layer> )

Example

=MINA( Internet Sales[Shipping] )

PROSTITUTE

Gets the minimum value after evaluating each row expression in a specified table.

Syntax

MINX(<table>, <expression>)

Example

=MINX( FILTER(InternetSales ) , [Sales Territory Code ] = 2 , [Load] )

other functions

• maximum
• ERA
• MAXX
• TOTAL
• AVERAGE
• SUMX
• AVERAGE

BoxPower Be Desktop Tutorial

#two. count functions

DISTINCTCOUNT

Gets the count of distinct numbers, avoiding any replication.

Syntax

DISTINCTCOUNT( <Spalte> )

Example

=DISTINCTCOUNT(ProductList[ProductID])

SAY

Gets the total number of items even if there are repetitions.

Syntax

COUNT(<column>)

Example

=COUNT([Shipped Date])

GRAF

Gets the number of elements in a non-empty column.

Syntax

COUNT(<Column>)

Example

= COUNTA( 'ProductSalesperson'[Phone] )

TEETH

Gets the number of rows in a specified table.

Syntax

ACCOUNTS( < table > )

Example

=COUNTERS('Requests')

Access here to find out difference betweenframe against powerRBI

#3. Date and time functions

GIVEN

Retrieves the desired date in datetime format.

Syntax

DATE(<year>, <month>, <day>)

Example

= DATE (27.02.2020)

HOUR

Recovers hours not AM PM format.

Syntax

TIME (<datetime>)

Example

=TIME('Orders' [transaction time])

HOY

Gets the current date.

Syntax

HOY()

Example

=YEAR(TODAY())-2012

other functions

• NOW
• EMONAT

Access here to find out Power BI Slicer: A Complete Guide

#4. mathematical functions

Abdomen

Gets the absolute value.

Syntax

ABS( <Number> )

Example

=ABS( [LabelPrice] - [SalePrice] )

Exp

Gets the value of the exponent.

Syntax

EXP( <Number> )

Example

=EXP( [Performance] )

MADE

Gets the factorial of a given number.

Syntax

DONE(<Number>)

Example

=DONE([Values])

other functions

• LN
• PROTOCOL
• Pi
• PERFORMANCE
• QUOTIENT
• SIGN
• square

read thisQuestions and answers about the latest Power BI interviewsthat help you get well-paying jobs

#5. logical functions

mi

Performs a logical conjunction on two specified expressions.

Syntax

AND( <logical1> , <logical2> )

Example

=IF( AND(1<2 , 2<3) , "All true", "One or false" )

O

Performs logical disjunction on two specified expressions.

Syntax

O( <logical1 > , <logical2 > )

Example

=( IF(OR(1<2, 2<3) , "All true", "One or more false")

NO

Performs the logical negation on the specified expression.

Syntax

NOT (<logical>)

Example

=NO([product prices])

other functions

• SE
• SEERRO

For more detailed information about Power BI, visitPower BI Desktop Tutorial

#6. information functions

IT IS EMPTY

Declares whether the value is empty or not true or false.

Syntax

EMPTY (<Value>)

Example

=SE( EM BLANK ('CalculatedActions'[Last Year Total Sales]), BLANK() , (CalculatedActions'[Last Year Total Sales]/'CalculatedActions'[Last Year Total Sales])

IS NUMBER

Indicates whether the value is a number or not.

Syntax

NUMBER(<value>)

Example

=IF(ISNUMBER(2), "Is number", "Is not number" )

SNOTEXT

Declare if the value is not text.

Syntax

ISNOTEXT( <Value> )

Example

=IF( NOT TEXT(“ ”), “It is not text”, “It is text”)

other functions

• ERROR
• ISTEXTO

#7. text functions

THE TOOL

Make the union of two ropes.

Syntax

THE TOOL( <text1> , <text2> )

Example

= CONCATENATE( „Hello“ , „Student“ )

FESTIVAL

Rounds the number to a specified decimal place.

Syntax

FIXED( <number>, <decimal places>, <no_commas>)

Example

=FIXED([LabelPreis], 3,1)

SUBSTITUTE

Replaces part of a string with the specified characters.

Syntax

REPLACE( <old_text> , <start_num>, <num_chars> , <new_text> )

Example

=REPLACE('New services'[service ID],2,3, "AB" )

Other features:

• LOOK FOR
• THE TOOL
• LOOK FOR
• SUPERIOR, SUPERIOR

Also read:Power BI services and benefits

Create a metric with Power BI DAX

The following steps will help you create a metric with Power BI DAX.

1. First go toPower BI desktop app.

2. Right-click or hover over the Sales tableMore options(...) ofcampoCampo.

3. When the menu appears, selectnew measure. This activity saves the new measurement inofferTable. You can also create a new metric in the Power BI desktop ribbon as follows.

GonnaHeimguide >calculationsgroup >New measure.

4. Then above the report area is the formula bar where you can edit your name and write an Xpression data analysis formula.

Before creating the formula, let's discuss the concept. This makes it easier to continue

Selection of variables and creation of formulas:

• The net quantity of products or services requires a few variables. I then listed the necessary variables: net worth, quantity of products or services, price of each quantity, and other expenses (full cost price).
• Net Value = (Total Amount of Sales) * (Per/Product or Service) - Expense (Cost Price).

5. The metric is named by default in the formula bar, but we need to make the metric more prominent, highlight the metric in the formula bar and change it to "Net Worth".

6. When creating your formula, type Sum after the equals sign. The suggestion dropdown menu will now appear as soon as you type the beginning of the letter. If necessary, select from the list and pressForbidden.

7. Press Enter after entering the TOTAL. You can now see an open bracket along with a dropdown list of suggestions for existing columns. It can be passed to the SUM function.

8. The expression is always between the opening and closing parentheses. In this case, your expression includes an argument to pass to the SUM function: the SalesQuantity column. start writingamount of salesBisSales[SalesAmount]is the only value that appears in the list.

The table name preceded by the column name gets the full name of the column. This makes your formula easier to understand.

9. Choosesales [sales amount]in the list and type the closing parenthesis.

Multiply the value by the first expression and subtract the other column from the final value:

• After you finish the first expression, multiply 15,000 by this expression to get the total.

Sales[Sales quantity]* 15000.

• After the desired value, type a space, a space, a minus (-) operator, and another space.
• Write another SUM function and start writing TotalCost which you can chooseSales [Total Cost]and then type the closing parenthesis.

10. To exit and validate the formula, we must enterForbiddeno Select the check mark icon (commit).

the validatednet worthThe measure is now ready to use in the sales table infield area.

Diploma:

DAX is one of the most widely used expression syntax today with a wide scope. Learning opens up various opportunities in the marketplace, especially in SQL-enabled companies. With DAX in Power BI, you can create any type of formula or logical expression as needed.

Let us know in the comments if you want to learn and excel in DAX.

Do you have questions? Leave a comment below and we'll get in touch with you.