563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
How to write queries in DAX to interrogate SSAS tabular models Part four of an eight-part series of blogs |
---|
As well as creating measures to aggregate data in tabular models using DAX, you can also write queries to extract data - this blog shows you how!
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
It's rare that you'll want to list all of the rows in a table: more commonly, you'll want to summarise data.
The DAX SUMMARIZE function is so similar to the concept of SELECT ... GROUP BY in SQL that you may wonder why Microsoft couldn't have merged the two language features!
In DAX you can summarise by one or more fields in a table, and then show an aggregation for each unique combination of values. Here's the basic syntax:
By far the easiest way to understand this is to look at the examples which follow below.
This query would show the total quantity sold by species:
-- show total quantity by species
EVALUATE
SUMMARIZE (
Species,
Species[SpeciesName],
"Quantity sold",
SUM ( 'Transaction'[Quantity] )
)
This query should give the following results:
The total quantity column has been renamed to Quantity sold, as requested.
The following query would show the number of transactions and quantity sold by species, animal and product:
-- show two statistics
-- summarised by 3 columns
EVALUATE
SUMMARIZE (
Product,
Species[SpeciesName],
Animal[AnimalName],
Product[ProductName],
"Number of transactions",
COUNTROWS ( 'Transaction' ),
"Quantity sold",
SUM ( 'Transaction'[Quantity] )
)
Here's what you should get when you run this query:
The query groups by 3 columns, and shows two statistics.
Just as in SQL, you can use the ROLLUP function to force DAX to calculate all subtotals and totals. For example:
-- show total sales for each
-- year and species
EVALUATE
SUMMARIZE (
'Transaction',
ROLLUP (
Calendar[Year],
Species[SpeciesName]
),
"Total qty",
SUM ( 'Transaction'[Quantity] )
)
Here's what this would show:
You can include as many or as few columns as you like in the ROLLUP function.
You can use the ISSUBTOTAL function with ROLLUP to determine for any column whether it is being used for grouping, subtotalling or totalling (although you'd have to care a lot more than this owl about the roll-up feature to do so).
So far we've been considering all the rows in tables; what I'll do in the next part of this blog is to show how to filter data.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
From: | gveg |
When: | 10 Oct 17 at 11:21 |
How would I summarize by date? For example, if I want to sum all sales up till beginning of this month, then sum by every day of the current month - how would I do that?
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 2023. All Rights Reserved.