🧙♂️ DAX Functions: Your Magic Spells for Power BI
Universal Analogy: Imagine you’re a chef in a magical kitchen. DAX functions are your special recipes. Each recipe (function) takes ingredients (data) and creates something delicious (insights)!
🌟 What Are DAX Functions?
Think of DAX functions like magic words you whisper to Power BI. You say the magic word, give it some ingredients, and POOF! It gives you exactly what you need.
Real-Life Example:
- Mom asks: “How old is Grandma?”
- You think: TODAY’s date minus Grandma’s birthday = Age!
- That’s exactly what DAX does with data!
📅 DAX Date Functions
Date functions help you work with calendar dates. Think of them as your time machine controls.
TODAY() - What Day Is It?
Returns today’s date. That’s it. Super simple!
CurrentDate = TODAY()
What it does: Gives you today’s date, like looking at a calendar.
NOW() - Exact Time Right Now
CurrentMoment = NOW()
Returns the date AND the time. Like checking your watch AND calendar together!
DATE(year, month, day) - Build a Date
Create any date you want, piece by piece.
MyBirthday = DATE(2020, 5, 15)
Like saying: “Make me the date May 15, 2020!”
YEAR(), MONTH(), DAY() - Take Dates Apart
Break a date into pieces. Like opening a present to see what’s inside!
BirthYear = YEAR([BirthDate])
BirthMonth = MONTH([BirthDate])
BirthDay = DAY([BirthDate])
Example: If BirthDate is May 15, 2020:
- YEAR gives you: 2020
- MONTH gives you: 5
- DAY gives you: 15
EOMONTH() - End of the Month
Find the last day of any month. Super helpful for deadlines!
MonthEnd = EOMONTH([OrderDate], 0)
The 0 means “same month.” Use 1 for next month, -1 for last month.
DATEDIFF() - Days Between Dates
Count the days (or months, or years) between two dates.
DaysOld = DATEDIFF([StartDate], [EndDate], DAY)
Like counting: “How many days until my birthday?”
⏰ Time Intelligence Functions
These are your time travel functions! They help you compare now with the past.
TOTALYTD() - Total for This Year
Adds up everything from January 1st until today.
SalesYTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date])
Story: Imagine counting all the cookies you ate this year. Start from January, keep adding until today!
SAMEPERIODLASTYEAR() - Last Year, Same Time
Look at the same time period, but one year ago.
LastYearSales = CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Dates[Date])
)
Like asking: “What was I doing exactly one year ago today?”
PREVIOUSMONTH() - Last Month
Get data from the previous month.
LastMonthSales = CALCULATE(
SUM(Sales[Amount]),
PREVIOUSMONTH(Dates[Date])
)
PREVIOUSYEAR() - Last Year
Get data from the previous year.
PriorYearTotal = CALCULATE(
SUM(Sales[Amount]),
PREVIOUSYEAR(Dates[Date])
)
DATEADD() - Shift Time Forward or Back
Move dates by any amount you choose.
Sales3MonthsAgo = CALCULATE(
SUM(Sales[Amount]),
DATEADD(Dates[Date], -3, MONTH)
)
-3 MONTH means “go back 3 months.”
PARALLELPERIOD() - Compare Same Periods
ParallelQtr = CALCULATE(
SUM(Sales[Amount]),
PARALLELPERIOD(Dates[Date], -1, QUARTER)
)
-1 QUARTER means “same period, one quarter ago.”
graph TD A["📅 Pick a Date"] --> B{What do you need?} B --> C["Same time last year?"] B --> D["Total so far this year?"] B --> E["Move back in time?"] C --> F["SAMEPERIODLASTYEAR"] D --> G["TOTALYTD"] E --> H["DATEADD"]
📝 DAX Text Functions
Text functions are like word games. They help you play with letters and words!
CONCATENATE() - Glue Words Together
Stick two pieces of text together.
FullName = CONCATENATE([FirstName], [LastName])
Input: “Emma” + “Watson” Output: “EmmaWatson”
Wait, we need a space! Use this instead:
FullName = [FirstName] & " " & [LastName]
Output: “Emma Watson” ✓
LEFT(), RIGHT(), MID() - Cut Out Letters
Like using scissors on words!
FirstThree = LEFT([ProductCode], 3)
LastTwo = RIGHT([ProductCode], 2)
MiddlePart = MID([ProductCode], 4, 3)
Example: ProductCode = “ABC123XYZ”
- LEFT gives: “ABC” (first 3)
- RIGHT gives: “YZ” (last 2)
- MID(4,3) gives: “123” (starting at position 4, take 3 characters)
LEN() - Count Letters
How long is this word?
NameLength = LEN([CustomerName])
“Emma” → 4 letters
UPPER(), LOWER(), PROPER() - Change Case
Shouting = UPPER("hello") → "HELLO"
Whisper = LOWER("HELLO") → "hello"
Polite = PROPER("john doe") → "John Doe"
TRIM() - Remove Extra Spaces
Gets rid of spaces at the start and end.
CleanName = TRIM(" Emma ")
Output: “Emma” (no extra spaces!)
SUBSTITUTE() - Replace Words
Swap out one thing for another.
NewText = SUBSTITUTE([Category], "Old", "New")
“Old Shoes” becomes “New Shoes”
SEARCH() and FIND() - Hunt for Letters
Find where something is hiding in text.
Position = SEARCH("@", [Email])
If Email is “emma@email.com”, SEARCH returns 5 (the @ is at position 5).
SEARCH ignores uppercase/lowercase. FIND cares about uppercase/lowercase.
🤔 DAX Logical Functions
Logical functions help your computer make decisions, just like you do!
IF() - Make a Choice
The most important logical function. “If this is true, do this. Otherwise, do that.”
Status = IF([Sales] > 1000, "Great!", "Keep trying")
Story:
- You ask: “Did I sell more than 1000?”
- Yes? Show “Great!”
- No? Show “Keep trying”
AND() - Both Must Be True
BothTrue = IF(
AND([Sales] > 1000, [Profit] > 100),
"Winner!",
"Not quite"
)
Like asking: “Did you clean your room AND finish homework?” Both must be true to get dessert!
OR() - At Least One True
EitherTrue = IF(
OR([Sales] > 1000, [IsVIP] = TRUE),
"Special Customer",
"Regular"
)
Like asking: “Do you have a ticket OR a VIP pass?” Either one works!
NOT() - Flip the Answer
Turns TRUE into FALSE, and FALSE into TRUE.
NotCompleted = NOT([IsCompleted])
SWITCH() - Multiple Choices
Better than many IF statements!
Grade = SWITCH(
TRUE(),
[Score] >= 90, "A",
[Score] >= 80, "B",
[Score] >= 70, "C",
[Score] >= 60, "D",
"F"
)
Story: It’s like a vending machine. Put in a number, get out a grade!
IFERROR() - Handle Mistakes Gracefully
When something goes wrong, show a nice message instead of an error.
SafeDivide = IFERROR([Sales] / [Quantity], 0)
If you try to divide by zero (oops!), just show 0 instead of an ugly error.
ISBLANK() - Is It Empty?
Check if a cell has nothing in it.
HasValue = IF(ISBLANK([Phone]), "No Phone", "Has Phone")
COALESCE() - First Non-Blank Value
Returns the first value that isn’t empty.
ContactInfo = COALESCE([Phone], [Email], "No Contact")
Tries Phone first. If blank, tries Email. If both blank, shows “No Contact”.
graph TD A["🤔 Need a Decision?"] --> B{How many choices?} B --> C["Two choices"] B --> D["Many choices"] C --> E["Use IF"] D --> F["Use SWITCH"] E --> G{Need multiple conditions?} G --> H["All true? → AND"] G --> I["Any true? → OR"]
🎯 Quick Recipe Card
| What You Need | Recipe (Function) | Example |
|---|---|---|
| Today’s date | TODAY() | =TODAY() |
| Year from date | YEAR() | =YEAR([Date]) |
| Days between | DATEDIFF() | =DATEDIFF(A,B,DAY) |
| This year’s total | TOTALYTD() | =TOTALYTD(SUM(…)) |
| Last year same time | SAMEPERIODLASTYEAR() | =CALCULATE(…) |
| Join text | & or CONCATENATE | ="Hello " & “World” |
| First letters | LEFT() | =LEFT([Text], 3) |
| Count letters | LEN() | =LEN([Name]) |
| Make a choice | IF() | =IF(A>B, “Yes”, “No”) |
| Multiple choices | SWITCH() | =SWITCH(TRUE(),…) |
| Handle errors | IFERROR() | =IFERROR([X]/[Y], 0) |
🌈 You Did It!
Now you know the four magical categories of DAX functions:
- 📅 Date Functions - Control time and dates
- ⏰ Time Intelligence - Travel through time to compare periods
- 📝 Text Functions - Play with words and letters
- 🤔 Logical Functions - Make smart decisions
Remember: Every expert was once a beginner. Keep practicing, and soon these functions will feel as natural as riding a bike!
Pro Tip: Start with IF() and TODAY(). These two alone will solve 50% of your problems! 🚀