Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

Help in choosing a DAX training course

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).

Calculated Columns

You can use DAX to create formulae for new columns in a table:

Calculated column

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.

Measures

Suppose you want to show for each region its sales as a percentage of the highest sales for any region:

Regional sales as % of max

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).

Date-based Calculations

To use date-based formulae in DAX, it's best first to set up a calendar table:

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:

Year to date sales

The last column shows the sales to date for the year, 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.
This page has 0 threads Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...