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!

  1. Writing DAX queries
  2. DAX and SQL compared
  3. The EVALUATE command in the DAX query language
  4. Using SUMMARIZE to group or aggregate DAX query data
  5. Filtering in DAX queries using CALCULATETABLE or FILTER
  6. Adding columns in a DAX query using ADDCOLUMNS
  7. Combining the results of two or more tables (this blog)
  8. 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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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






This would show for each species the animals it contains:

Species and animals

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




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

Showing two-legged animals

The results of running the query.


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

-- show only those species with two-legged

-- animals corresponding




CALCULATETABLE ( Animal, Animal[Legs] = 2 )


This would then give the following results:

Using GENERATE - 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




CALCULATETABLE ( Animal, Animal[Legs] = 2 )


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

All combinations

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.

This blog has 0 threads Add post