Top 10 DAX Expressions in Power BI

As a Power BI trainer at Intellisoft Systems, and writer of several Excel & Power BI articles, I’ve worked with with over 15,000 business professionals, data analysts, and executives over the years.

When people first approach Power BI, they often marvel at how it brings their data to life with interactive dashboards and visualizations.

But as they dive deeper, they soon realize that Power BI’s true power lies not just in its visuals, but in its ability to manipulate, analyze, and derive insights from data using DAX (Data Analysis Expressions).

My Journey with DAX in Power BI

I remember my own journey into the world of DAX, which felt a lot like learning a new programming language. At first, the syntax was daunting, and it seemed like every time I thought I understood how a function worked, a new scenario would throw me off.

However, as I started teaching Power BI Courses around the world in our public classes at Intellisoft, and as I assisted hundreds of data analysts in creating their Business Intelligence Dashboards in Power BI, I noticed a pattern.

The same set of DAX expressions came up repeatedly as crucial for solving business problems, answering analytical questions, or building complex reports.

The beauty of DAX is that, once you grasp the core concepts, it opens up endless possibilities for data transformation and modeling. It’s not just about creating formulas—DAX helps you enhance your reports by extracting hidden patterns and making data-driven decisions easier.

With Power Query to Clean the Data, and DAX to enhance the analysis of data, it is the best of both worlds.

Why Create DAX Expressions?

In the world of data analytics, basic reports often don’t tell the full story. As professionals, we are expected to go beyond the surface-level numbers and reveal patterns & insights that drive business strategy.

Here’s why DAX expressions play such an essential role in this:

1. Advanced Calculations: DAX allows you to perform calculations that Excel or standard report-building tools can’t easily handle. Whether it’s aggregating data across different periods, creating year-over-year growth metrics, or applying conditional logic, DAX gives you that edge.
2. Data Relationships: Power BI thrives on relationships between datasets. DAX lets you define those relationships more clearly, ensuring that your reports reflect the real connections within your data.
3. Custom Metrics: Every business has unique key performance indicators (KPIs) and metrics. DAX allows you to create custom measures that align with your organization’s needs, ensuring your reports are tailored to the business context.
4. Dynamic Reporting: DAX enables dynamic filtering and context-driven calculations. This allows you to create reports that respond in real-time to user inputs, making your dashboards far more interactive and meaningful.
5. Enhanced Performance: While Power BI’s drag-and-drop interface makes it easy to start, large datasets or complex models can slow things down. Writing efficient DAX expressions can optimize your data models, improve performance, and ensure that your reports scale with your data.

Top 10 Most Useful DAX Expressions in Power BI

Over the years, I’ve seen which DAX expressions are most commonly used and how they help professionals derive actionable insights. Here are the top 10 DAX expressions that every serious data analyst, executive, or manager should know:

1. CALCULATE()
   The most versatile DAX function. It lets you modify the context in which a calculation is performed. Use it to create measures that behave differently based on filters.Example:
   CALCULATE([Total Sales], 'Calendar'[Year] = 2023)
2. SUMX()
   A powerful iteration function. SUMX allows you to perform row-by-row calculations across tables. It’s essential for scenarios where you need to evaluate expressions in each row before summing the result.Example:
   SUMX(Sales, Sales[Quantity] * Sales[Price])
3. FILTER()
   Used to return a table of data based on a filter expression. It’s particularly useful when working with complex conditions that you can’t handle with basic filtering.Example:
   FILTER(Sales, Sales[Region] = "East" && Sales[Amount] > 10000)
4. RELATED()
   This function retrieves related data from another table. It’s handy when you have a relationship between tables and need to pull in values from related tables.
   
   Example:
   RELATED(Products[Category])
5. DIVIDE()
   A safer way to perform division, it handles cases where division by zero would otherwise cause an error. It’s more reliable than the simple “/” operator.Example:
   DIVIDE([Total Sales], [Total Units], 0)
6. ALL()
   Removes any filters that may be applied to a column or table. This is useful when you need a calculation that ignores certain filters, such as when calculating overall totals.Example:
   CALCULATE([Total Sales], ALL(Sales[Region]))

7. RANKX()
   Allows you to rank items based on a specified expression. It’s invaluable for comparing performance, such as ranking sales by regions or products.Example:
   RANKX(ALL(Sales[Region]), [Total Sales])

8. EARLIER()
   Often used in calculated columns, EARLIER enables row-by-row context in calculations where you need to refer to previous row contexts.Example:
   CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Product] = EARLIER(Sales[Product])))

9. SWITCH()
   Functions like a multi-conditional IF statement, allowing you to return different values based on specific criteria.Example:
   SWITCH([Rating], 1, "Poor", 2, "Average", 3, "Good", "No Rating")

10. DATESYTD()
    A time intelligence function that calculates the year-to-date total of a measure. It’s essential for tracking cumulative performance over time.Example:
    DATESYTD('Calendar'[Date])

Microsoft is adding new DAX functions almost every month. There are already over 800+ DAX functions, whereas in Excel there are only about 400+ functions. Whatever calculation you are planning to build, there is most likely a DAX function that can be used to simplify the expression. It is worth learning new DAX functions and reviewing the entire function list to checkout the functions you can use immediately.

Conclusion

Mastering DAX is a continuous learning journey. It can seem overwhelming at first, but once you understand its logic and structure, you’ll realize how powerful it is in creating actionable insights and making sense of complex data.

In my Power BI classes, I always encourage participants to experiment with these expressions, building them into their regular reporting process. Once you start using DAX effectively, you’ll wonder how you ever managed without it.

Creating DAX expressions in Power BI allows you to push your data analysis skills to new heights. Whether you’re an analyst diving deep into data trends, or a manager looking for quick, actionable insights, these top DAX functions will empower you to extract the maximum value from your data models.

Most business communication happens via email with virtual offices, global clients, and multiple time zones. Yet, most people struggle to write great, effective and professional emails.

The result:  People judge you by your emails. If the email seems unprofessional or buggy with mistakes, your and the company’s reputation is immediately tarnished.

Fortunately, writing compelling and professional emails within a few minutes is extremely easy. Just remember to take note of the following tips.

1.  Subject lines: Always use a simple, to-the-point subject line. Ideally, it should be no longer than 5-7 words. Most people look at the subject line to decide if they are going to open, and read the email, or simply delete it, irrespective of the content.

2. Greet: Always provide a simple greeting at the beginning of the email. It could simply be Hi Susan, or Hello Richard.

3. Thank for something: If this is a follow-up email on something, or you are replying to someone’s email, it is a good idea to thank the person for replying to you. Thanks a lot for getting back to me so quickly. Thanks for your time on the phone, or Thanks for the meeting. By thanking the person, you make it a more pleasant email, and the recipient is put at ease with your nice comments.

4. Reason / Objective of Email: The key reason for writing the email should be very clear and concise. There is no need to write long-winded emails, as no one has time to read that much. People nowadays scan emails to quickly find the most important thing, and then decide what to do with it. You should quickly come to the point, like

We need to meet for a short while to discuss and resolve xyz, or
I need your help to review xyz, or I am writing with regards to xyz.

5. What you Want them to do / Ask: People scan emails to find out if they have to do anything about it – keep it, file it for the future, or take any action.  If you want them to do something for you, you must state it clearly. If this is not clear, then they won’t do anything. Keep it short, simple and easily scanable. For example,

• I want you to arrange a meeting between the 3 parties – possibly by the 15th of the month.
• I would appreciate it if you could review the attached proposal and get back to me by the 15th Nov.
• Could you please reply which time suits you better – Monday at 11am or Tuesday at 4pm? 

By putting an action, and an action by date, you clarify what the recipient has to do, and by when. There is no room for ambiguity, and you are more likely to get what you wanted, by the due date.

6. Add Closing Remarks: It is a good idea for you to thank the recipient one more time, and add some nice, polite closing remarks, like:

• Thanks for your help and support.
• Thanks for your cooperation and support.
• Feel free to contact me if you have any questions or concerns.  I look forward to hearing from you.

7. Signature: It is a must to have a simple yet clear signature. You should not end with Cheers or See Ya unless it is a friend you are writing to. For business writing, you must always say, With Best Regards, or Sincerely, or Thank You. Regards, Richard.

8. Spell Check & Grammar Check: It is essential to always do a spelling check and a grammar check. Nothing spoils a good email than a few typos. All good email packages have a built in spell check. You should make use of it, and even set your email configuration to always do a spell check before sending. This can make your emails more professional and set up you in the eyes of the recipient.

Common Pitfalls in Writing Professional Emails & How To Avoid Them:

• Writing in All Capital Letters: Writing in all caps is considered offensive, rude on the internet. You should write Subjects in Title Case to stand out. The email body should be using normal English language rules – first letter is in capital, and the rest in small case. Use appropriate punctuation, and avoid using too many exclamation marks or question marks etc. It looks quite amateurish.
• Using Vague Subject lines: Do not try to fool the recipient to click your email by sending suspicious email subject lines or shady ones. Click for BONUS, or Find the Good Stuff <<<-CLICK HERE, or SEE WHAT JOHNNY DOES TO GET THE PASSES are quite spammy, and should be avoided.
• Attaching a huge image or attachment: Don’t attach huge files as attachments unless really required. You can always upload the file to some server or drobox etc. and provide them with the link to download if they wish to. This way you won’t become their enemy for jamming their mailbox with huge emails.
• Not Using Professional language: While you could use the English language to the fullest, and use big words, it is not always recommended. You should write emails as if a high school kid is going to read it. Most people’s vocabulary is not huge, and they don’t read much books or learn new things beyond school years. Some experts go so far to say that you should stick to a Primary 6th grade English to be perfectly clear to everyone.
• Not Using Formatting, Paragraphs, Headings: Do space out your emails with paragraphs, and points, or headings. Don’t write in huge block paragraphs of 8 to 10 lines or more in each block. It becomes pretty difficult to read, and you can lose a lot of people from getting your message. Use bold or underlined text in some important areas to make them stand out, but don’t bold every thing.
• Lengthy emails That Are Not To the Point: Write only as much as you need. Don’t start a long winded email that goes on and on. We are writing an email, not a sales page.
• Use of Emoticons: For processional emails, it is not recommended to use any emoticons. So don’t try to act cute on business emails. Stay to the point, clear, and professional as possible.

Related Training:

Learn the Art of Professional Business Email Writing & Email Etiquette in our 2 day WSQ Funded workshop.

Full of exercises and practical examples, it is a totally immersive workshop that will work wonders to your email writing skills. Do check it out here: Writing Professional Emails