Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
In this page
We run several courses which cover DAX but they all cover three main things, as listed under separate headings below.
All of the diagrams below are taken from Power BI Desktop, but they would work equally well in PowerPivot or Analysis Services (Tabular Model).
You can use DAX to create formulae for new columns in a table:
This formula labels each purchase as Mean or Generous, according to the amount spent.
This formula shows that initially DAX looks quite similar to Excel - but appearances are, in this case, deceptive.
Suppose you want to show for each region its sales as a percentage of the highest sales for any region:
The South East region here has the highest sales at 18,506 units.
Here's a measure to do this:
Percent of highest regional sales =
-- first get the maximum of the total sales for all regions
VAR HighestRegionalSales =
CALCULATE(
-- get the highest value ...
MAXX(
-- ... from the table of sales by region ...
SUMMARIZE (
Region,
[RegionName],
"Total sales",
SUM(Purchase[Quantity])
),
-- ... of the sales
[Total sales]
),
-- do this for all regions
ALL(Region)
)
RETURN
IF(
-- if we're filtering by the region, divide
-- this region's total sales by sales for the
-- highest region; otherwise, show a blank (this
-- will make sure there's nothing in the total row)
ISFILTERED(Region[RegionName]),
DIVIDE(
SUM(Purchase[Quantity]),
HighestRegionalSales
),
BLANK()
)
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 | Return the current filter context |
EARLIER | Refer to other rows in a table |
The phrase "filter 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).
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:
Year to date = TOTALYTD(
SUM(Purchase[Quantity]),
Calendar[DateKey]
)
Here's what this measure might show for example:
The last column shows the sales to date for the year, using the measure we've created.
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.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.