WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 529 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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!

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

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.