This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to write queries in DAX to interrogate SSAS tabular models Part seven 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! Writing DAX queries DAX and SQL compared The EVALUATE command in the DAX query language Using SUMMARIZE to group or aggregate DAX query data Filtering in DAX queries using CALCULATETABLE or FILTER Adding columns in a DAX query using ADDCOLUMNS Combining the results of two or more tables (this blog) Other useful DAX functions This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

How to write queries in DAX to interrogate SSAS tabular models

Part seven 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!

Writing DAX queries
DAX and SQL compared
The EVALUATE command in the DAX query language
Using SUMMARIZE to group or aggregate DAX query data
Filtering in DAX queries using CALCULATETABLE or FILTER
Adding columns in a DAX query using ADDCOLUMNS
Combining the results of two or more tables (this blog)
Other useful DAX functions

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 February 2016

Combining the results of two or more tables

You can combine results from two tables using GENERATE, GENERATEALL or CROSSJOIN.

GENERATE corresponds to a SQL inner join; GENERATEALL to a SQL outer join.

Using GENERATEALL to combine tables

The GENERATEALL function allows you to show for every row in one table the related rows in another:

-- show for each species

-- its list of animals

EVALUATE

GENERATEALL (

Species,

RELATEDTABLE(Animal)

)

This would show for each species the animals it contains:

The four species, with the animals each contains.

Filtering the data from one of the combined tables

Typically, you use GENERATE or GENERATEALL to show only certain matching rows for each main table row. For example, the following query lists out each row from the species table, together with the two-legged animals for each species:

-- show every species, and the two-legged

-- animals corresponding to it

EVALUATE

GENERATEALL (

Species,

CALCULATETABLE ( Animal, Animal[Legs] = 2 )

)

This would give the following results. Because we've used GENERATEALL, we also get the species which have no two-legged animals (mammals, reptiles and amphibians):

The results of running the query.

Comparing GENERATE and GENERATEALL

You could run the query above using GENERATE instead of GENERATEALL, like this:

-- show only those species with two-legged

-- animals corresponding

EVALUATE

GENERATE (

Species,

CALCULATETABLE ( Animal, Animal[Legs] = 2 )

)

This would then give the following results:

The GENERATE function corresponds to an inner join in SQL, so we only see species which have two-legged animals.

Using CROSSJOIN to show all combinations of tables

The CROSSJOIN function mimics the SQL CROSS JOIN statement, and is (in my humble opinion) about as useful. Here's an example:

-- show every species, and every two-legged

-- animal

EVALUATE

CROSSJOIN(

Species,

CALCULATETABLE ( Animal, Animal[Legs] = 2 )

)

What this variant on our query would show is every species, and for each species every two-legged animal:

Struggling to see why this would be useful? Me, too.

As I delve further into exotic DAX commands, I'll finish this blog by showing the ROW and CONTAINS functions.

Parts of this blog
Writing DAX queries DAX and SQL compared The EVALUATE command in the DAX query language Using SUMMARIZE to group or aggregate DAX query data Filtering in DAX queries using CALCULATETABLE or FILTER Adding columns in a DAX query using ADDCOLUMNS Combining the results of two or more tables (this blog) Other useful DAX functions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

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

BLOGS BY TOPIC

BLOGS BY AUTHOR

Combining the results of two or more tables

Using GENERATEALL to combine tables

Filtering the data from one of the combined tables

Comparing GENERATE and GENERATEALL

Using CROSSJOIN to show all combinations of tables

Head office

London

Manchester