Articles Tutorials Interview Questions

What is Dax in Power BI? | A Comprehensive Guide

Last updated on 28th Jan 2023, Artciles, Blog, Business Analytics

About author

Kimaya (Business Analytics Analyst )

Kimaya is the Sr.Business Analytics Analyst with 5+ years of experience. She has expertise in ABC analysis, SPI, Factory Overhead, R&D Capex, sunk cost, economic order quantity (EOQ), and EAC. Her articles assist in sharing information and abilities in core fields and provide students with informative knowledge.

(5.0) | 19887 Ratings 3014
    • In this article you will learn:
    • 1.DAX Basics in Power BI: What is DAX?
    • 2.Importance of DAX in Power BI.
    • 3.DAX in Power BI: How does it work?
    • 4.Power BI DAX Basics: Types of a functions in DAX.
    • 5.Conclusion.

DAX Basics in Power BI: What is DAX?

Functions, constants and operators are used in a DAX to create expressions. In simpler terms, DAX is an advanced version of MS Excel with high-end data manipulation and management capabilities. It is developed by a Microsoft to interact with its business intelligence and data modeling tools like a PowerPivot Power BI.

Some of major advantages of Power BI :

  • A Real-time data visualization.
  • Creation of a charts and report generation on voice command using a Cortana.
  • Large storage space and simple retrieval of a data from the storage.
  • Higher security at a Row level.
  • BI has intuitive user experience features and memorizes a most frequently used trends.

All these functions are an essentially performed by a pre-set tools and commands in a system. But there are few occasions when want to perform a specific type of function says a certain type of a data analysis but you are unable to do so because a command does not pre-exist in the system. What do do in such a case? This is where a DAX comes into picture.

Importance of DAX In Power BI:

DAX is quite an important syntax as working in a DAX improves auser experience by deploying functionalities like a data visualization data importing and manipulating. For a normal report creation a basic knowledge of the dashboard is enough but to be able to build up sophisticated and insightful reports DAX is needed With several commands and measures that can be used under a DAX syntax the reports generated are more intuitive and discerning.

DAX in Power BI: How does it work?

DAX works on the combination of three fundamental concepts:

1. Syntax

2. Context

3. Functions

These inputs are the fundamental to the creation of a specific command that are fetches desired results.

1. Syntax:

Syntax refers to a components of the formula are making. It is the language used in a formula like the command, sign, operators, destination column or row or table etc. For example name, parenthesis, summation, name of a table etc.

2. Context:

Context refers to a target row that has been included in formula for a data retrieval or calculation. Context is present in a two types: Row Context and Filter Context.

3. Functions:

Functions refer to a predefined or already existing commands in a system. For example a Sum a Add a True a False etc.

Features of Power BI

Power BI DAX Basics: Types of a Functions in DAX:

1. Aggregate Functions:

Min: Fetches a minimum value in given column.

Syntax :

  • MIN(<"column">)
  • Example
  • =MIN( [ SellerMargin] )

Mina: Fetches a minimum value along with an Aggregate Functions Logical values and text representation of a numbers if any.

Syntax:

  • MINA( <'column'> )
  • Example
  • =MINA( InternetSales[Freight] )

Minx: Fetches a minimum value after evaluation of every row expression in a given table.

Syntax:

  • MINX ( < table >, <'expression'> )
  • Other functions
  • MAX
  • MAXA
  • MAXX
  • SUM
  • AVERAGE
  • SUMX
  • AVERAGEX

2. Count Functions:

Distinctcount: Fetches a count of distinct numbers are avoiding any replication.

Syntax:

  • DISTINCTCOUNT( <'column'> )
  • Example
  • =DISTINCTCOUNT( ProductsList[ProductID] )

Count: Fetches a total count of items even if repetitions are be present.

Syntax:

  • COUNT( <'column'> )
  • Example
  • =COUNT ( [ShipDate] )

Counta: Fetches a count of items in non-empty column.

Syntax:

  • COUNTA( <'column'> )
  • Example
  • = COUNTA( ‘ProductSeller’[Phone] )

Countrows: Fetches a number of rows in given table.

Syntax:

  • COUNTROWS( < table > )
  • Example
  • =COUNTROWS( ‘Enquiries’ )

3. Date-Time Functions :

Date: Fetches a desired date in Date-time format.

Syntax:

  • DATE ( <'year'>, <'month'>, <'day'> )
  • Example
  • =DATE ( 2020,02,27 )

Hour: Fetches hours in a AM PM format.

Syntax:

  • HOUR ( <'datetime'> )
  • Example
  • =HOUR( ‘Orders’ [TransactionTime] )

Today: Fetches a current date.

Syntax:

  • TODAY()
  • Example
  • = YEAR ( TODAY())-2012
  • Other functions
  • NOW
  • EOMONTH
Dax Functions

4. Math Functions:

Abs: Fetches an absolute value.

Syntax:

  • ABS( <'number'> )
  • Example
  • =ABS( [LabelPrice] – [SellingPrice] )

Exp: Fetches an exponent’s value.

Syntax:

  • EXP( <'number'> )
  • Example
  • =EXP( [Power] )

Fact: Fetches a factorial of a given number.

Syntax:

  • FACT( <'number'> )
  • Example
  • =FACT( [Values] )
  • Other functions
  • LN
  • LOG
  • PI
  • POWER
  • QUOTIENT
  • SIGN
  • SQRT

5. Logical Functions :

AND: A logical conjunction is performed on two specified expressions.

Syntax:

  • AND( <'logical1'> , <'logical2' > )
  • Example
  • =IF( AND(1<2 , 2<3) , “All true” , “One or false” )

OR: A logical disjunction is performed on two specified expressions.

Syntax:

  • OR( <'logical1' > , <'logical2' > )
  • Example
  • =( IF(OR(1<2 , 2<3) , “All true” , “One or more false” )

NOT: A logical negation is performed on a given expression.

Syntax:

  • NOT( <'logical' > )
  • Example
  • =NOT( [ProductPrices] )
  • Other functions
  • IF
  • IFERROR

6. Information Functions:

Isblank: Declares whether the value is blank or not as true or false.

Syntax:

  • ISBLANK( <'value'> )
  • Example
  • =IF( ISBLANK(‘CalculatedMeasures’[PreviousYearTotalSales]) ,BLANK() , (CalculatedMeasures’[PreviousYearTotalSales]/ ‘CalculatedMeasures’[PreviousyearTotalSales])

Isnumber: Determines whether a value is a number or not.

Syntax:

  • ISNUMBER(<'value'>)
  • Example
  • =IF( ISNUMBER(2), “Is number”, “Is Not number” )

Isnontext: Determines whether a value is a number or not.

Syntax

  • ISNONTEXT( <'value'> )
  • Example
  • =IF( ISNONTEXT(“ ”), “Is Non-Text”, “Is Text” )
  • Other functions
  • ISERROR
  • ISTEXT

7. Text Functions:

Concatenate: This function joins two strings.

Syntax:

  • CONCATENATE( <'text1'> , <'text2'> )
  • Example
  • = CONCATENATE( “Hello” , “Learner” )

Fixed: Rounds a number to the specified decimal.

Syntax:

  • FIXED( <'number'> , <'decimals'> , <'no_commas'> )
  • Example
  • =FIXED( [LabelPrice], 3,1 )

Replace: Replaces a portion of a string with the characters specified.

Syntax:

  • REPLACE( <'old_text'> , <'start_num'>, <'num_chars'> , <'new_text'> )
  • Example
  • =REPLACE( ‘New Services’[Service ID],2,3,”AB” )
  • Other functions
  • SEARCH
  • CONCATENATEX
  • SEARCH
  • UPPER

Conclusion:

DAX is one of the most widely used expression syntaxes today with a wide range of applications. Learning it opens up several opportunities in the market particularly in SQL-enabled businesses with DAX in Power BI you can create any type of logical expression or formula that you need.

Enquiry Now