Nested TOPCOUNT between 2 attributes within same dimension using GENERATE function not giving desired results

Question

Hi,

I am trying to achieve top 5 subcategories for each of the category in top 5 categories. I have used GENERATE and TOPCOUNT as shown in the below code for achieving the result. But it isn’t working as expected. Instead of returning top 5 sub-categories for each category it is returning 5 records in total

Below is screenshot from Adventure Works cube, where we can see each category has more than 5 sub-categories.

Below is the attribute relationship on the product dimension. Category and subcategory are directly related as shown below.

When I use subcategory and model name, we get proper results. These two attributes are not directly related.

Selecting top10 model names in each sub-category.

Selecting top5 model names in each sub-category.

Below is the screenshot from Adventure Works cube for sub-category and model name

Is this expected behavior? Or am I missing anything? Or is this a bug? Can anyone please provide inputs on the scenario. I have used Adventure Works cube to demonstrate the actual scenario we are working on.

Regards,

Anusha

Answer 1

You misplaced a crossjoin when copying the example from the Generate documentation page