Help in choosing a DAX training course
We run several courses which cover DAX, including Power BI Desktop, Analysis Services (Tabular), PowerPivot and Programming in DAX, but they all cover three main things, as listed under separate headings below.
All of the diagrams below are taken from PowerPivot within Excel, but they would work equally well in Power BI Desktop or Analysis Services (Tabular Model).
Calculated Columns
Just like in Excel, you can use DAX to create formulae for new columns in a table:
This formula labels each purchase as Mean or Spendthrift, according to the amount spent.
This formula shows that initially DAX looks quite similar to Excel - but appearances are, in this case, deceptive.
Measures
Suppose you have a pivot table like this (remember, this would work just as well for a Power BI Desktop matrix, for example):
A pivot table showing total sales by region and quarter.
You want to show each figure as a percentage of the London figure:
Reassuringly, the London row gives 100% for each quarter.
You could do this by creating a measure like this:
=SUM([Quantity]) /
CALCULATE(
SUM([Quantity]),
tblRegion[RegionName]="London"
)
Some of the main functions that you can use in measures are as follows:
| Function | What it allows you to do |
|---|---|
| SUM, AVERAGE, etc | Aggregate values for a column |
| SUMX, AVERAGEX, etc | Aggregate values for an expression |
| CALCULATE | Calculate an expression with a filter applied |
| FILTER | Pick out only certain rows in a table |
| VALUES | Keep the current query context |
| EARLIER | Refer to other rows in a table |
The phrase "query context" above isn't explained further here, but is the key to understanding DAX (and will obviously be explained on any of our DAX courses).
Date-based Calculations
To use date-based formulae in DAX, it's best first to set up a calendar table:
Part of a calendar table.
This allows you to create DAX measures using functions with fantastic names like these:
| DAX function | What it shows |
|---|---|
| SAMEPERIODLASTYEAR | The value of the current "cell", but for the same period (day, week, month or quarter) in the previous year. |
| TOTALQTD | Quarterly sales to date. |
| PARALLELPERIOD | The value of the same statistic, but for a particular year, quarter, month or date in the past. |
For example, this DAX measure would show cumulative sales to date for each time period:
=TOTALYTD(
SUM([Quantity]),
tblCalendar[DateKey]
)
Here's what this measure would show for our example:
The second column shows the sales to date, using the measure we've created.
DAX Queries
In addition to creating calculated columns and measures, our specialist DAX course will also show you how to write queries like this:
-- show most expensive transactions first (for
-- two or more transactions having same price,
-- sort by quantity sold)
EVALUATE
( 'Transaction' )
ORDER BY
'Transaction'[Price] DESC,
'Transaction'[Quantity]
DAX queries are similar to SQL queries, and allow you to extract blocks of data from tables or Analysis Services cubes.
We hope this has given you an idea of what you'll learn on our Power BI courses, but please feel free to contact us if you have any questions.