Want to Write Better DAX in Power BI?
Master These Proven Practices
Learning DAX isn’t just about writing code — it’s about writing efficient, readable, and reusable logic.
Here are the core practices I follow and recommend:
🔹 Use Explicit Measures Instead of Implicit Ones
Avoid dragging numeric fields directly into visuals (implicit measures)
Define your own using SUM, COUNT, etc.
DAX
Total Sales = SUM(Sales[Amount])
📌 Explicit measures make your logic reusable across visuals, easier to apply filters or time
intelligence, and help keep the data model organized.
🔹 Avoid Calculated Columns (When Possible)
Calculated columns increase model size
Use measures unless row-level context is required
📌 Measures are better for performance and flexibility
🔹 Use Clear, Consistent Naming Conventions
Avoid generic names like Measure1, Calc2
Use descriptive names like:
• Total Revenue
• Customer Count - Active
• Sales Growth YOY
📌 Improves collaboration and understanding
🔹 Use Variables (VAR) for Readability & Performance
Break logic into smaller parts
Avoid repeating expressions
DAX
VAR Current = SUM(Sales[Amount])
VAR Previous = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))
RETURN DIVIDE(Current - Previous, Previous)
📌 Using variables (VAR) makes your DAX easier to debug, keeps the logic
cleaner, and improves performance.
🔹 Use CALCULATE() Wisely
One of the most powerful DAX functions
Changes the filter context of your expression
Learn how it works before using it in complex logic
📌 With great power comes great responsibility!
🔹 Organize Measures into Display Folders
Use folders like:
• Revenue KPIs
• Customer Metrics
• Time Intelligence
📌 Keeps your model neat and easy to navigate
🔹 Understand Context: Row vs Filter
Row Context: In calculated columns and iterators
Filter Context: In visuals and slicers
📌 Knowing the difference changes how you write DAX
🔹 Use DAX Studio / Performance Analyzer for Optimization
DAX Studio → check query performance
Performance Analyzer → identify slow visuals/measures
📌 Helps make your reports faster and smarter
I’m still learning and growing — sharing helps me remember better.
💬 What’s your favorite DAX practice? Drop it in the comments!
#PowerBI #DAX #DataAnalytics #DAX #PowerBIDeveloper #MicrosoftPowerBI #DataModeling
🎯 Want to master Excel like a pro? Start by learning formulas the smart way — category by
category.
Excel isn't just rows and columns — it's logic, automation, and problem-solving at your fingertips.
If you're just starting out or want to refresh your basics, this post is for you👇
Here are the most useful Excel formulas and functions, grouped by category — with syntax and real-
world meaning.
📌 1. Math & Arithmetic Functions
These help you perform quick calculations.
SUM(range) → Adds all numbers
✅ =SUM(A1:A5)
AVERAGE(range) → Calculates average
✅ =AVERAGE(B2:B10)
ROUND(number, num_digits) → Rounds a number
✅ =ROUND(12.567, 1) → 12.6
ABS(number) → Returns absolute value
✅ =ABS(-10) → 10
INT(number) → Rounds down to nearest integer
✅ =INT(5.9) → 5
📌 2. Text Functions
Use these to clean, combine, or extract text.
CONCAT(text1, text2, …) → Joins text
✅ =CONCAT(A1, B1)
TEXT(value, format_text) → Formats a number/date as text
✅ =TEXT(TODAY(), "dd-mmm-yyyy")
LEFT(text, num_chars) → Gets characters from left
✅ =LEFT("Excel", 2) → Ex
RIGHT(text, num_chars) → Gets characters from right
✅ =RIGHT("Excel", 3) → cel
LEN(text) → Counts characters
✅ =LEN("Power") → 5
📌 3. Logical Functions
Make decisions using TRUE/FALSE logic.
IF(logical_test, value_if_true, value_if_false)
✅ =IF(A1>50, "Pass", "Fail")
AND(condition1, condition2) → All must be TRUE
✅ =AND(A1>10, B1<20)
OR(condition1, condition2) → Any one TRUE
✅ =OR(A1>100, B1=50)
NOT(logical) → Reverses the result
✅ =NOT(TRUE) → FALSE
📌 4. Lookup & Reference Functions
Pull info from other areas in your sheet.
VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
✅ =VLOOKUP("John", A2:C10, 2, FALSE)
HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
✅ =HLOOKUP(90, A1:E2, 2, TRUE)
INDEX(array, row_num, [column_num])
✅ =INDEX(A2:C5, 2, 3)
MATCH(lookup_value, lookup_array, [match_type])
✅ =MATCH(42, A1:A5, 0)
📌 5. Date & Time Functions
Make your work smarter with date logic.
TODAY() → Current date
✅ =TODAY()
NOW() → Current date and time
✅ =NOW()
DATEDIF(start_date, end_date, unit) → Difference between two dates
✅ =DATEDIF(A1, B1, "Y") → years
EDATE(start_date, months) → Adds months
✅ =EDATE(TODAY(), 3)
📌 6. Statistical Functions
Great for analysis, especially in finance and reporting.
COUNT(range) → Number of numeric entries
✅ =COUNT(A1:A10)
COUNTA(range) → Counts non-empty cells
✅ =COUNTA(A1:A10)
MAX(range) → Largest number
✅ =MAX(B2:B10)
MIN(range) → Smallest number
✅ =MIN(B2:B10)
MEDIAN(range) → Middle value
✅ =MEDIAN(B2:B10)
📌 7. Error Handling Functions
Clean up your reports with better logic.
IFERROR(value, value_if_error)
✅ =IFERROR(A1/B1, "Error")
ISERROR(value) → Checks if it’s an error
✅ =ISERROR(A1/B1)
✨ Whether you're working on reports, cleaning data, or building dashboards — these formulas will
save you time and stress.
💬 Which formula do you use the most in Excel? Or is there one that still confuses you?
Let’s learn together 📊💡
#Excel #ExcelFormulas #SpreadsheetTips #MicrosoftExcel #DataSkills #LearningInPublic
🧠 Already know the basics of Excel? It’s time to unlock its real power.
A few months ago, I thought I had "mastered" Excel because I knew VLOOKUP, SUM, and IF. But when
I started working on real-world projects — dashboards, automation, data analysis — I realized Excel
was way deeper than I imagined.
That’s when I discovered advanced functions — and everything changed.
If you’re ready to go beyond basics and think like an Excel analyst, here are advanced Excel
functions, grouped by category, that will help you work smarter, not harder 👇
📌 1. Lookup & Reference (Advanced)
For dynamic data retrieval and relationship building between datasets.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],
[search_mode])
✅ Replaces VLOOKUP & HLOOKUP with more flexibility
INDEX(array, row_num, [column_num])
✅ Returns value from a specific row and column
MATCH(lookup_value, lookup_array, [match_type])
✅ Returns the position of a value in a range
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
✅ Advanced version of MATCH
CHOOSE(index_num, value1, [value2], …)
✅ Returns value from a list based on a number
INDIRECT(ref_text)
✅ Converts text to a reference
OFFSET(reference, rows, cols, [height], [width])
✅ Returns a dynamic reference based on offsets
📌 2. Text Functions (Advanced)
Useful for data cleaning, transformation, and pattern matching.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
✅ Joins text from multiple ranges with a delimiter
FILTERXML(xml, xpath)
✅ Parses structured data (often used with web scraping)
SUBSTITUTE(text, old_text, new_text, [instance_num])
✅ Replaces occurrences of text
TEXTSPLIT(text, col_delimiter, [row_delimiter]) (Excel 365)
✅ Splits text into columns/rows
TEXTAFTER(text, delimiter, [instance_num])
✅ Returns part of a text after a delimiter
TEXTBEFORE(text, delimiter, [instance_num])
✅ Returns part of a text before a delimiter
📌 3. Logical Functions (Advanced)
For creating powerful decision-making logic.
IFS(condition1, value1, [condition2, value2], …)
✅ Replaces nested IFs with cleaner logic
SWITCH(expression, value1, result1, [default])
✅ Cleaner alternative to multiple IFs
LET(name1, name_value1, calculation)
✅ Define variables in your formula for readability/performance
LAMBDA(parameter, calculation)
✅ Create custom functions without VBA
📌 4. Math & Trigonometry (Advanced)
For analytical modeling and dashboards.
SUMPRODUCT(array1, [array2], …)
✅ Multiplies and sums arrays — powerful for weighted calculations
MOD(number, divisor)
✅ Returns remainder — useful for grouping/cycles
CEILING(number, significance)
✅ Rounds up to the nearest multiple
FLOOR(number, significance)
✅ Rounds down to the nearest multiple
RANDARRAY([rows], [columns], [min], [max], [integer]) (Excel 365)
✅ Generates arrays of random numbers
📌 5. Date & Time (Advanced)
Great for dynamic time intelligence in reports.
WORKDAY(start_date, days, [holidays])
✅ Returns a future workday excluding weekends/holidays
NETWORKDAYS(start_date, end_date, [holidays])
✅ Counts workdays between dates
EOMONTH(start_date, months)
✅ Gets end of month date
SEQUENCE(rows, [columns], [start], [step]) (Excel 365)
✅ Generates an array of sequential numbers
📌 6. Dynamic Arrays & Filtering (Excel 365)
For modern Excel users using Excel 365 or Excel 2021.
FILTER(array, include, [if_empty])
✅ Dynamically filters data
SORT(array, [sort_index], [sort_order], [by_col])
✅ Sorts a range or array
SORTBY(array, by_array1, [order1], …)
✅ Sort based on one or more arrays
UNIQUE(array, [by_col], [exactly_once])
✅ Returns unique values
VSTACK(array1, array2, …)
✅ Stacks arrays vertically
HSTACK(array1, array2, …)
✅ Stacks arrays horizontally
DROP(array, rows, [columns])
✅ Removes specified rows/columns
TAKE(array, rows, [columns])
✅ Returns top rows/columns
📌 7. Statistical & Data Analysis
For data analysis, forecasting, and summaries.
FORECAST.LINEAR(x, known_y's, known_x's)
✅ Predict future values
RANK.EQ(number, ref, [order])
✅ Ranks a number in a dataset
PERCENTILE.INC(array, k)
✅ Returns the k-th percentile
STDEV.P(range) / STDEV.S(range)
✅ Measures variation in data
COVARIANCE.P(range1, range2)
✅ Measures covariance
CORREL(array1, array2)
✅ Correlation between two variables
Formula Description Example
SUM(range) Adds numbers =SUM(A1:A5)
AVERAGE(range) Calculates average =AVERAGE(B2:B10)
=ROUND(12.567,
ROUND(num, digits) Rounds a number
1) → 12.6
ABS(num) Absolute value =ABS(-10) → 10
Rounds down to
INT(num) =INT(5.9) → 5
integer
