DAX Functions in Power BI

In the world of data analysis, the ability to transform raw data into actionable insights is crucial. Power BI, a leading business analytics tool, empowers users to create interactive reports and dashboards. At the heart of Power BI’s analytical prowess lies DAX (Data Analysis Expressions), a collection of functions, operators, and constants designed to perform data analysis and calculations. In this blog, we will delve into the essential DAX functions in Power BI  that every Power BI user should master to elevate their data analysis capabilities.

What is DAX?

DAX is a formula language used in Power BI, Power Pivot, and Analysis Services. It enables users to create custom calculations and aggregations, enhancing the data modeling and visualization process. DAX functions are similar to Excel functions but are optimized for use with relational data and dynamic aggregations.

Essential DAX Functions

1. SUM
   • Description: Adds all the numbers in a column.
   • Usage: TotalSales = SUM(Sales[SalesAmount])

2. AVERAGE
   • Description: Calculates the average of the numbers in a column.
   • Usage: AverageSales = AVERAGE(Sales[SalesAmount])

3. MIN
   • Description: Returns the smallest number in a column.
   • Usage: MinSales = MIN(Sales[SalesAmount])

4. MAX
   • Description: Returns the largest number in a column.
   • Usage: MaxSales = MAX(Sales[SalesAmount])

5. COUNT
   • Description: Counts the number of non-blank cells in a column.
   • Usage: CountSales = COUNT(Sales[SalesAmount])

6. COUNTROWS
   • Description: Counts the number of rows in a table.
   • Usage: RowCount = COUNTROWS(Sales)

7. DISTINCTCOUNT
   • Description: Counts the number of unique values in a column.
   • Usage: UniqueProductCount = DISTINCTCOUNT(Sales[ProductID])

8. IF
   • Description: Returns one value if a condition is true and another value if it’s false.
   • Usage: SalesCategory = IF(Sales[SalesAmount] > 1000, “High”, “Low”)

9. AND
   • Description: Check if both conditions are true.
   • Usage: HighProfitHighSales = IF(AND(Sales[SalesAmount] > 1000, Sales[Profit] > 500), “High”, “Low”)

10. OR
    • Description: Check if at least one condition is true.
    • Usage: HighProfitOrHighSales = IF(OR(Sales[SalesAmount] > 1000, Sales[Profit] > 500), “High”, “Low”)

11. NOT
    • Description: Reverses the logical value of its argument.
    • Usage: IsNotHighSales = NOT(Sales[SalesAmount] > 1000)

12. BLANK
    • Description: Returns a blank value.
    • Usage: ReturnBlank = BLANK()

13. ISBLANK
    • Description: Checks if a value is blank.
    • Usage: IsSalesAmountBlank = ISBLANK(Sales[SalesAmount])

14. YEAR
    • Description: Extracts the year from a date.
    • Usage: Year = YEAR(Sales[OrderDate])

15. MONTH
    • Description: Extract the month from a date.
    • Usage: Month = MONTH(Sales[OrderDate])

For Free, Demo classes Call: 075074 14653

Registration Link: Power BI Training in Pune!

1. TODAY
   • Description: Returns the current date.
   • Usage: Today = TODAY()

2. DATEDIFF
   • Description: Calculates the difference between two dates.
   • Usage: DaysBetween = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)

3. CONCATENATE
   • Description: Combines two text strings into one.
   • Usage: FullName = CONCATENATE(Employee[FirstName], ” ” & Employee[LastName])

4. LEFT
   • Description: Returns the specified number of characters from the start of a text string.
   • Usage: LeftPart = LEFT(Employee[FirstName], 3)

5. RIGHT
   • Description: Returns the specified number of characters from the end of a text string.
   • Usage: RightPart = RIGHT(Employee[LastName], 2)

6. SEARCH
   • Description: Finds one text value within another and returns its position.
   • Usage: Position = SEARCH(“Smith”, Employee[LastName], 1, 0)

7. UPPER
   • Description: Converts a text string to uppercase.
   • Usage: UppercaseName = UPPER(Employee[FirstName])

8. LOWER
   • Description: Converts a text string to lowercase.
   • Usage: LowercaseName = LOWER(Employee[LastName])

9. TRIM
   • Description: Removes all leading and trailing spaces from a text string.
   • Usage: TrimmedName = TRIM(Employee[FirstName])

10. REPLACE
    • Description: Replaces part of a text string with another text string.
    • Usage: ReplacedName = REPLACE(Employee[FirstName], 1, 1, “X”)

11. LEN
    • Description: Returns the number of characters in a text string.
    • Usage: NameLength = LEN(Employee[FirstName])

12. MID
    • Description: Returns a specific number of characters from a text string starting at the position you specify.
    • Usage: MiddleName = MID(Employee[FirstName], 2, 2)

13. CALCULATE
    • Description: Evaluate an expression in a modified filter context.
    • Usage: HighSales = CALCULATE(SUM(Sales[SalesAmount]), Sales[SalesAmount] > 1000)

14. FILTER
    • Description: Returns a table that represents a subset of another table or expression.
    • Usage: FilteredSales = FILTER(Sales, Sales[SalesAmount] > 1000)

15. ALL
    • Description: Removes all filters from a table or column.
    • Usage: AllSales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

Conclusion

Mastering DAX functions is key to unlocking the full potential of Power BI. By leveraging these functions, you can perform advanced data transformations, create dynamic aggregations, and generate insightful visualizations. Whether you’re a beginner or an experienced Power BI user, understanding and utilizing DAX functions will significantly enhance your data analysis capabilities. Explore the future of Power BI and its potential to thrive in an evolving market. Discover whether this powerful tool will remain a key player in data analytics.

Do watch our Channel to learn more: Click Here

Author:

Karishma Pawar

Call the Trainer and Book your free demo Class For Power BI Call now!!!
| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd.