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
In this basic Power BI DAX article, we'll cover the following topics |
What is DAX? Meaning of DAX in Power BI DAX in Power BI: How does it work?
Calculated Columns x Metrics Power BI DAX basics: types of functions in DAX
Create a metric with Power BI DAX |
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:
- Syntax
- context
- 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.
FAQs
What is the best way to learn DAX for Power BI? â€ş
The best way to learn DAX is to create some basic formulas, use it with some actual data, and see the results for yourself. The examples and tasks here use the Contoso Sample DAX Formulas.
How difficult is learn DAX for Power BI? â€şDAX is a really easy language to write but a bit hard to grasp in the beginning. Once you learn DAX underlying theory then it is easy to write any formula and you can play with multiple nested contexts. With every topic, first, we will discuss hidden concepts and then we will do its implementation.
Should I learn DAX for Power BI? â€şData analysts who require higher flexibility in their data analysis should learn DAX. DAX allows a user to create custom columns and calculations that can provide deeper insight into a business problem. However, in general, all users who intend to utilize the full functionalities of Power BI well should learn DAX.
Should I learn DAX or M? â€şM is the language used in Power Query (you don't have to use M directly, but it makes your life easier in the long run). DAX is the language used once your data is in Power BI to create calculated columns and measures.
What are the three fundamental concepts of DAX? â€şWe'll frame our understanding of DAX around three fundamental concepts: Syntax, Functions, and Context.
Is DAX like Excel? â€şDAX formulas are very similar to Excel formulas. To create one, you type an equal sign, followed by a function name or expression, and any required values or arguments.
Is SQL harder than DAX? â€şThis makes DAX very abstract regarding both the underlying data structure, the query context, and exactly what it's doing under the hood. If SQL is 90% concrete and 10% abstract, DAX is 5% concrete and 95% abstract. Its abstract nature makes learning DAX much more challenging.
Why is DAX not easy? â€şReason #1: DAX is a functional language
Functional languages are extremely elegant, from a mathematical point of view. Yet, they are also very counter-intuitive. As humans, when we describe an algorithm, we follow steps. We do not provide a function that computes the result: we provide the steps to reach the result.
PQ calculation slows down data load, where as DAX calculated column will slow down visual update on interaction with slicer etc. Most cases, if you do data load during after hours (or early in the morning) once. It's more desirable to have better performance during interaction with report.
Is DAX a coding? â€şData Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.
Is DAX used in SQL? â€ş
By using SQL Server Management Studio (SSMS), Power BI Report Builder, and open-source tools like DAX Studio, you can create and run your own DAX queries. DAX queries return results as a table right within the tool, allowing you to quickly create and test the performance of your DAX formulas.
Is DAX similar to Tableau? â€şDAX allows for CALCULATETABLE etc, to return virtual table, that can be filtered. Which Tableau lacks... In short, DAX allows for more complex data manipulation and is more powerful than what Tableau offers. Though it is quite a bit harder to learn.
Is DAX a good skill? â€şIt Makes You a Better Data Professional
Even though DAX can only be used in an environment that supports it, the skill of knowing how to use DAX goes well beyond its scope.
DAX is not a programming language. DAX is primarily a formula language and is also a query language. You can use DAX to define custom calculations for Calculated Columns, Measures, Calculated Tables, Calculation Groups, Custom Format Strings, and filter expressions in role-based security in Tabular models.
How many DAX formulas are there? â€şThe DAX function reference provides detailed information including syntax, parameters, return values, and examples for each of the over 250 functions used in Data Analysis Expression (DAX) formulas.
How do you write a DAX query? â€ş- The name of the measure or calculated column.
- The equal-to operator (â€ś=â€ť) indicates the start of the formula.
- A DAX function.
- Opening (and closing) parentheses (â€ś()â€ť)
- Column and/or table references.
- Note that each subsequent parameter in a function is separated by a comma (â€ś,â€ť)
Key differences between DAX and Excel functions
There are over 250 DAX functions or â€śexpressionsâ€ť, some of which mirror those found in Excel and others that aggregate and draw relationships between data in more advanced ways. As well as offering additional functions, DAX supports more data types than Excel.
DAX includes many functions that return a table, rather than a value. The table is not displayed in a reporting client, but is used to provide input to other functions. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
Which is faster M or DAX? â€şBy this article it seems that M is faster.
Is M language difficult to learn? â€şOnce you know the syntax, then everything becomes simple. M is a language that you can learn it's syntax easily.