How to Start your DAX Journey

If you start to learn any language. It takes a lot of time to learn and internalize all the things associated with it. I have been working with DAX for over 4 years now.

Recently Someone asked me if I had to learn DAX from scratch, how would I go about it. That’s when I thought of writing this down and sharing it with you.

From DataCamp

Basics and Syntax:

DAX(Data Analysis Expressions) — It is a library of functions and operators that you use to write formulas that return some values that you can use either in POWER BI, or Power Pivot in Excel Data Models. You can write Measures, Calculated columns, and Row level security. (more on this later).
Before you create your own formulas, let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written

This formula includes the following syntax elements:

A. Name — Total Sales.
B. When calculated, it will return a result.
C. The DAX function SUM, adds up all of the numbers in the Sales[SalesAmount] column. SUM(More about it later).
D. Parenthesis (), which surrounds an expression that contains one or more arguments. Most functions require at least one argument. An argument passes a value to a function
E. The referenced table, Sales.
F. The referenced column, [SalesAmount], is in the Sales table. With this argument, the SUM function knows which column to aggregate a SUM.

When trying to understand a DAX formula, it’s often helpful to break down each of the elements into a language you think and speak every day. For example, you can read this formula as:

For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.

CODE IS NOTHING BUT JUST GIVING INSTRUCTIONS. YOU ASK SOMETHING AND IT RETURNS SOMETHING. IF YOU UNDERSTAND THIS BASIC THING, IT WILL BE EASIER FOR YOU TO LEARN ANY LANGUAGE.

Resources and Most Used Formulas

Remember these return some values, either it will be a table or a single value, or a column. You do need to worry about this for now. These are the most used formulas that I use most on a daily basis.

FORMULAS:

1. Calculate — Expression that returns some value that you can apply a filter on.
2. Switch — Use 2 DAX measures in one.
3. Selectedvalue — When you want to select a particular value and return something.
4. SUM, SUMX, Count, CountX, Max, Min, etc. — Basic Aggregate Functions
5. Calendar — Return a table of the date field that you passed into it.
6. Summarize and join — Similar to group by in SQL and joining multiple tables.
7. All, All Selected, Remove Filters, Keep Filters.
8. Variables

How to start:

There are some youtube channels and articles that I am going to outlay so that the learning journey becomes smooth for you.

1. After learning the basics here, go to the @curbal youtube channel, go through the first 7–8 videos on different functions, and familiarize yourself.
2. Open Power BI, After that, Find open-source data to that you can apply these functions. (here is the sample code you can use) or go to ourworldindata.org and download any file and start practicing
3. Import the data and start your DAX journey.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  genre TEXT,
  rating FLOAT
);

INSERT INTO books (title, author, genre, rating) VALUES
  ('The Intelligent Investor', 'Benjamin Graham', 'Finance', 4.5),
  ('Security Analysis', 'Benjamin Graham and David L. Dodd', 'Finance', 4.5),
  ('The Essays of Warren Buffett', 'Warren Buffett', 'Finance', 4.5),
  ('The Little Book of Common Sense Investing', 'John C. Bogle', 'Finance', 4.5),
  ('One Up on Wall Street', 'Peter Lynch', 'Finance', 4.5),
  ('The Total Money Makeover', 'Dave Ramsey', 'Personal Finance', 4.5),
  ('Rich Dad, Poor Dad', 'Robert T. Kiyosaki', 'Personal Finance', 4.5),
  ('Think and Grow Rich', 'Napoleon Hill', 'Personal Development', 4.5),
  ('The Millionaire Next Door', 'Thomas J. Stanley and William D. Danko', 'Personal Finance', 4.5),
  ('Your Money or Your Life', 'Vicki Robin and Joe Dominguez', 'Personal Finance', 4.5),
  ('The Compound Effect', 'Darren Hardy', 'Personal Development', 4.5),
  ('The Simple Path to Wealth', 'JL Collins', 'Personal Finance', 4.5),
  ('The Bogleheads’ Guide to Investing', 'Taylor Larimore, Mel Lindauer, and Michael LeBoeuf', 'Finance', 4.5),
  ('The Compound Effect', 'Darren Hardy', 'Personal Development', 4.5),
  ('The Simple Path to Wealth', 'JL Collins', 'Personal Finance', 4.5),
  ('The Bogleheads’ Guide to Investing', 'Taylor Larimore, Mel Lindauer, and Michael LeBoeuf', 'Finance', 4.5),
  ('The Millionaire Fastlane', 'MJ DeMarco', 'Personal Finance', 4.5),
  ('The Millionaire Real Estate Investor', 'Gary Keller and Dave Jenks', 'Real Estate Investing', 4.5),
  ('Unshakeable', 'Tony Robbins', 'Personal Finance', 4.5),
  ('The Total Money Makeover', 'Dave Ramsey', 'Personal Finance', 4.5)

RESOURCES:

Here is the list of resources that you should follow and learn from regularly in your DAX journey:
1. SQLI BI
2. Curbal BI Youtube channel
3. How to Power BI
4. Goodly Youtube channel

TIPS FOR MASTERING DAX AND MASTERING COMPLEX DAX FUNCTIONS

As you can see from the below image, I have used almost all the functions mentioned above in a single DAX code. Th goal here is not to brag but to show how it will be used in your day-to-day work when you start working on DAX.
You will be provided with business case statements that you have to write a dax statements for that.

Ref1

Ref 2

Initially, it may seem very complex when you understand it, but It boils down to solving business statements and functionalities.

Here is a functionality that you can try and apply yourself.
— You have multiple Columns such as SALES, COST, and UNITS and You have multiple Dimensions Such as Product, Sub Product, and State. You want to find out the YTD, The Last 12 Month, Current Month values of the above-mentioned fields in a single DAX statement.
Let me know your code in the comments.

As they say, clear the fundamentals and everything else can be learned along the way. This was written just to provide you with the basics of DAX.

That’s It. I hope it helps you in your journey of becoming a #data analyst. I have used and used most of these websites not just for practice but for revision of multiple aspects of the data analytics journey.

If you have any questions or any inputs you want to share, just comment or hit me up on LinkedIn or Twitter. I am pretty much active there.

All the best.

Please Start. You will not regret it.

Thanks,

Piyush Jain