How to use various DAX functions in Power BI

Last updated on Sep 27 2021
Bhavin Mukherjee

Table of Contents

How to use various DAX functions in Power BI

DAX Introduction

DAX (Data Analysis Expressions) is a formula expression language and can be used in different BI and visualization tools. DAX is also known as function language, where the complete code is kept inside a function. DAX programming formula contains two data types: Numeric and Other. Numeric includes – integers, currency and decimals, while Other includes: string and binary object.
Subsequent is an example of DAX formula, which uses a function to calculate a column in a table.

Page 1 Image 1 17
DAX formula

DAX function can also include other functions, conditional statements, and value references.

DAX Functions

In Power BI, you’ll use different function types to analyze data, and create new columns and measures. It includes functions from different categories such as −
• Aggregate
• Text
• Date
• Logical
• Counting
• Information
Power BI provides an simple way to see the list of all functions. When you start typing your function with within the formula bar, you’ll see the list of all functions starting with that alphabet.

Page 2 Image 2 16
Function List

Aggregate Functions

DAX features a number of aggregate functions.
• MIN
• MAX
• Average
• SUM
• SUMX

Counting Functions

Other counting functions in DAX include −
• DISTINCTCOUNT
• COUNT
• COUNTA
• COUNTROWS
• COUNTBLANK

Logical Functions

Subsequent are the collection of Logical functions −
• AND
• OR
• NOT
• IF
• IFERROR

TEXT Functions

• REPLACE
• SEARCH
• UPPER
• FIXED
• CONCATENATE

DATE Functions

• DATE
• HOUR
• WEEKDAY
• NOW
• EOMONTH

INFORMATION Functions

• ISBLANK
• ISNUMBER
• ISTEXT
• ISNONTEXT
• ISERROR

DAX Calculation Types

In Power BI, you’ll create two primary calculations using DAX −
• Calculated columns
• Calculated measures
When you navigate to the Modeling tab, you’ll see a New Column option at the top of the screen. This also opens the formula bar where you’ll enter DAX formula to perform the calculation. DAX – Data Analysis Expression is a powerful language used in Excel to perform calculations. You’ll also rename the column by changing the Column text withwithin the formula bar.

Page 4 Image 3 3
New Column

Withwithin the subsequent example, we have made a new column: Product Code (Product_C), which is derived from the last 3 characters of Prod_Id column. Subsequent is the formula −

Product_C = RIGHT( Sheet1[Prod_Id],3)

To create a calculated measure, navigate to New Measure tab under Modeling. This will add a new object under the Fields tab with the name Measure.

Page 4 Image 4 2
New Measure
Page 5 Image 5 2
Measure

You’ll write DAX formula to calculate the worth of the new measure, as we did for the new calculated column.
So, this brings us to the end of blog. This Tecklearn ‘How to use various DAX functions in Power BI ’ blog helps you with commonly asked questions if you are looking out for a job in Power BI. If you wish to learn Power BI and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI

• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources

Power BI Workflow

• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships

Power Query for Data Transformation

• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations

Filters and Drill Down Report

• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report

Power Pivot for Data Modelling

• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions

Data Analysis Expression – DAX Queries

• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features

Power BI Desktop & Administration

• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters

Introduction to Power Q & A

• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

 

0 responses on "How to use various DAX functions in Power BI"

Leave a Message

Your email address will not be published. Required fields are marked *