Scribd
Upload
2 views4 pages

Dax Formulas Guide

This document serves as a practical reference for commonly used DAX formulas in Power BI, categorized into various functions such as aggregation, logical, filter, time intelligence, relationship, iterator, text, date & time, mathematical, and table functions. It highlights the input requirements for each function and provides examples for better understanding. Additionally, it emphasizes important DAX concepts and a recommended learning order from beginner to advanced levels.

Uploaded by

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

Dax Formulas Guide

This document serves as a practical reference for commonly used DAX formulas in Power BI, categorized into various functions such as aggregation, logical, filter, time intelligence, relationship, iterator, text, date & time, mathematical, and table functions. It highlights the input requirements for each function and provides examples for better understanding. Additionally, it emphasizes important DAX concepts and a recommended learning order from beginner to advanced levels.

Uploaded by

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

Most Commonly Used DAX Formulas

(with Input Requirements)


A practical reference for Power BI DAX formulas and the input types they require.

 Columns
 Tables
 Measures
 Filters
 Scalar values (single numbers/text/date)

1. Aggregation Functions
Used to summarize numerical data.
Function Purpose Input Needed Example
SUM() Adds values Single numeric column SUM(Sales[Amount])
AVERAGE() Mean value Numeric column AVERAGE(Sales[Profit])
MIN() Smallest value Column MIN(Sales[Price])
MAX() Largest value Column MAX(Sales[Price])
Counts non-empty numeric
COUNT() Numeric column COUNT(Sales[Quantity])
rows
COUNTA() Counts non-empty values Any column COUNTA(Customer[Name])
DISTINCTCOUNT(Customer
DISTINCTCOUNT() Unique values count Column
[ID])
COUNTROWS() Counts table rows Table COUNTROWS(Sales)

2. Logical Functions
Used for conditions and decision-making.
Function Purpose Input Needed Example
Logical test + true/false IF(Sales[Amount]>1000,"Hig
IF() Conditional logic
outputs h","Low")
Expression + result
SWITCH() Multiple conditions SWITCH([Grade],1,"A",2,"B")
mappings
AND() Both conditions true Two logical conditions AND(A>0,B<10)
OR() Any condition true Two logical conditions OR(A>0,B<10)
NOT() Reverses logic Logical expression NOT(Sales[Amount]>100)

3. Filter & Context Functions


Core of advanced Power BI calculations.
Function Purpose Input Needed Example
CALCULATE(SUM(Sales[A
CALCULATE() Changes filter context Expression + filters mount]),Region[City]="Nagp
ur")
FILTER(Sales,Sales[Amount
FILTER() Filters a table Table + condition
]>500)
ALL() Removes filters Table/column ALL(Sales)
Removes all except selected ALLEXCEPT(Sales,Sales[Re
ALLEXCEPT() Table + columns
filters gion])
Function Purpose Input Needed Example
VALUES(Customer[Country]
VALUES() Unique values table Column
)
SELECTEDVALUE(Product[
SELECTEDVALUE() Returns selected value Column
Category])
KEEPFILTERS(Product[Bran
KEEPFILTERS() Preserves existing filters Filter expression
d]="Nike")

4. Time Intelligence Functions


Very common in dashboards and reports.
Function Purpose Input Needed Example
TOTALYTD(SUM(Sales[Amo
TOTALYTD() Year-to-date total Expression + date column
unt]),Date[Date])
TOTALMTD(SUM(Sales[Am
TOTALMTD() Month-to-date Expression + date column
ount]),Date[Date])
TOTALQTD(SUM(Sales[Am
TOTALQTD() Quarter-to-date Expression + date column
ount]),Date[Date])
SAMEPERIODLASTYEAR(D
SAMEPERIODLASTYEAR() Previous year comparison Date column
ate[Date])
DATEADD(Date[Date],-1,YE
DATEADD() Shift dates Date column + interval
AR)
DATESYTD() YTD date table Date column DATESYTD(Date[Date])

5. Relationship Functions
Used when tables are related.
Function Purpose Input Needed Example
RELATED() Fetch related column value Related column RELATED(Customer[Name])
RELATEDTABLE() Related table rows Table RELATEDTABLE(Sales)
Activates inactive USERELATIONSHIP(Sales[
USERELATIONSHIP() Two columns
relationship ShipDate],Date[Date])
CROSSFILTER(A[ID],B[ID],
CROSSFILTER() Changes filter direction Two columns + direction
BOTH)

6. Iterator Functions (Very Important)


Operate row-by-row.
Function Purpose Input Needed Example
SUMX(Sales,Sales[Qty]*Sal
SUMX() Row-wise sum Table + expression
es[Price])
AVERAGEX(Sales,Sales[Pro
AVERAGEX() Row-wise average Table + expression
fit])
MAXX() Row-wise max Table + expression MAXX(Sales,Sales[Profit])
MINX() Row-wise min Table + expression MINX(Sales,Sales[Profit])
COUNTX(Sales,Sales[Amou
COUNTX() Row-wise count Table + expression
nt])
RANKX(ALL(Product),[Total
RANKX() Ranking Table + expression
Sales])

7. Text Functions
Used for cleaning and formatting.
Function Purpose Input Needed Example
CONCATENATE(Customer[
CONCATENATE() Join text Two text values
First],Customer[Last])
LEFT() Left characters Text + count LEFT(Product[Code],3)
RIGHT() Right characters Text + count RIGHT(Product[Code],2)
MID() Extract middle text Text + start + length MID(Name,2,4)
LEN() Text length Text LEN(Customer[Name])
UPPER() Uppercase Text UPPER(Customer[Name])
LOWER() Lowercase Text LOWER(Customer[Name])
TRIM() Remove spaces Text TRIM(Customer[Name])

8. Date & Time Functions


Useful for time-based calculations.
Function Purpose Input Needed Example
TODAY() Current date None TODAY()
NOW() Current datetime None NOW()
YEAR() Extract year Date YEAR(Sales[Date])
MONTH() Extract month Date MONTH(Sales[Date])
DAY() Extract day Date DAY(Sales[Date])
Start date + end date +
DATEDIFF() Difference between dates DATEDIFF(A,B,DAY)
interval

9. Mathematical Functions
Used in numeric calculations.
Function Purpose Input Needed Example
DIVIDE() Safe division Numerator + denominator DIVIDE([Profit],[Sales])
ROUND() Round value Number + decimals ROUND([Sales],2)
ABS() Absolute value Number ABS([Profit])
MOD() Remainder Number + divisor MOD(10,3)
POWER() Exponent Number + power POWER(2,3)

10. Table Functions


Used heavily in advanced modeling.
Function Purpose Input Needed Example
SUMMARIZE(Sales,Sales[R
SUMMARIZE() Group data Table + grouping columns
egion])
ADDCOLUMNS(Sales,"Profit
ADDCOLUMNS() Add calculated columns Table + expressions
",Sales[SP]-Sales[CP])
SELECTCOLUMNS(Sales,"
SELECTCOLUMNS() Select columns Table + column mappings
Amt",Sales[Amount])
UNION() Combine tables Two tables UNION(Table1,Table2)
INTERSECT() Common rows Two tables INTERSECT(A,B)

Most Important DAX Functions for Interviews


If you are preparing for Power BI interviews, focus heavily on:
1. CALCULATE()
2. FILTER()
3. ALL()
4. SUMX()
5. RELATED()
6. RANKX()
7. DIVIDE()
8. SELECTEDVALUE()
9. TOTALYTD()
10. SWITCH()
These functions separate beginner users from intermediate/advanced Power BI developers.

Important DAX Concept


The real difficulty in DAX is not memorizing formulas.
It is understanding:
 Row Context
 Filter Context
 Context Transition
 Relationships
 Evaluation Order
Once you understand these properly, most DAX becomes predictable instead of confusing.
The biggest turning point for most Power BI developers is understanding:
CALCULATE()

because it modifies filter context dynamically.


Example:
Total Sales Nagpur =
CALCULATE(
SUM(Sales[Amount]),
Customer[City] = "Nagpur"
)

Here CALCULATE() changes the context before evaluating the sum.

Beginner-to-Advanced DAX Learning Order


1. Basic Aggregations
2. IF / SWITCH
3. Measures vs Calculated Columns
4. CALCULATE
5. FILTER
6. ALL / ALLEXCEPT
7. Iterators (SUMX)
8. Time Intelligence
9. Ranking & Virtual Tables
10. Advanced Context Transition

You might also like