Scribd
Upload
156 views2 pages

powerBI Formula

The document is a cheat sheet for Power BI DAX formulas, covering various categories such as basic arithmetic, aggregation, logical, text, date & time, time intelligence, filtering, ranking, lookup, and miscellaneous functions. It provides examples for each function, demonstrating how to perform calculations, manipulate text, and filter data within Power BI. This resource serves as a quick reference for users to efficiently utilize DAX formulas in their data analysis tasks.

Uploaded by

Poornima Vr
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
156 views2 pages

powerBI Formula

The document is a cheat sheet for Power BI DAX formulas, covering various categories such as basic arithmetic, aggregation, logical, text, date & time, time intelligence, filtering, ranking, lookup, and miscellaneous functions. It provides examples for each function, demonstrating how to perform calculations, manipulate text, and filter data within Power BI. This resource serves as a quick reference for users to efficiently utilize DAX formulas in their data analysis tasks.

Uploaded by

Poornima Vr
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Power BI DAX Formulas Cheat Sheet

1. Basic Arithmetic

 Total Sales = SUM(Sales[Amount]) → Adds all values in the column

 Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost]) → Calculates profit

 Growth % = ([Current Year Sales] - [Previous Year Sales]) / [Previous Year Sales] → Growth
percentage

2. Aggregation Functions

 SUM(Column) → Adds all values

 AVERAGE(Column) → Returns the mean

 MAX(Column) → Returns the highest value

 MIN(Column) → Returns the lowest value

 COUNT(Column) → Counts numeric values

 DISTINCTCOUNT(Column) → Counts unique values

3. Logical Functions

 IF(Sales[Amount] > 1000, "High", "Low") → Returns "High" if Amount > 1000, else "Low"

 SWITCH([Category], "A", "Alpha", "B", "Beta", "Other") → Acts like a case statement

 IF(AND(Sales[Amount] > 1000, Sales[Profit] > 200), "Profitable", "Not Profitable")

4. Text Functions

 CONCATENATE(Sales[Product], " - ", Sales[Category]) → Joins text

 LEFT(Sales[Product], 3) → Extracts first 3 characters

 RIGHT(Sales[Product], 4) → Extracts last 4 characters

 MID(Sales[Product], 2, 3) → Extracts 3 characters from position 2

 SEARCH("Laptop", Sales[Product], 1, 0) → Finds position of "Laptop"

 SUBSTITUTE(Sales[Product], "Old", "New") → Replaces text

5. Date & Time Functions

 TODAY() → Returns current date

 NOW() → Returns current date and time

 YEAR(Sales[Date]) → Extracts year

 MONTH(Sales[Date]) → Extracts month

 DATEDIFF(Sales[Start Date], Sales[End Date], DAY) → Days between two dates

 EOMONTH(Sales[Date], 0) → End of the month


6. Time Intelligence

 TOTALYTD(SUM(Sales[Amount]), Sales[Date]) → Year-to-date total

 TOTALMTD(SUM(Sales[Amount]), Sales[Date]) → Month-to-date total

 TOTALQTD(SUM(Sales[Amount]), Sales[Date]) → Quarter-to-date total

 SAMEPERIODLASTYEAR(SUM(Sales[Amount]), Sales[Date]) → Previous year same period

 PARALLELPERIOD(SUM(Sales[Amount]), -1, MONTH) → Last month’s data

7. Filtering Functions

 CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Electronics") → Filters Electronics sales

 FILTER(Sales, Sales[Amount] > 1000) → Returns table of sales > 1000

 ALL(Sales[Region]) → Ignores filters on Region

 REMOVEFILTERS(Sales[Date]) → Removes all filters on Date

8. Ranking & Sorting

 RANKX(ALL(Sales[Product]), SUM(Sales[Amount]),,DESC, DENSE) → Ranks products by sales

 TOPN(5, Sales, Sales[Amount], DESC) → Returns top 5 highest sales

9. Lookup & Relationships

 RELATED(Products[Category]) → Returns related column from another table

 LOOKUPVALUE(Products[Category], Products[ProductID], Sales[ProductID]) → Finds


category from ProductID

10. Miscellaneous

 BLANK() → Returns a blank value

 ISBLANK(Sales[Amount]) → Checks if blank

 DIVIDE(SUM(Sales[Revenue]), SUM(Sales[Units]), 0) → Division with zero handling

You might also like