**Prologue...**

Firstly I would like to say that probably it would be easier to call this function Horizontal Dimensionality (or HDim or even SecDim)

This could clear some clouds - when you first see this function you may feel that this another level for dimensionality function.

(*for those who wants to know more about the dimensionality function please see this document*

** What it is used for?**

Returns the number of dimension pivot table rows that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates. This function is the equivalent of the dimensionality function for horizontal pivot table dimensions.

The secondarydimensionality() function always returns 0 when used outside of pivot tables.

**What does it mean?**

Where dimensionality shows number of **vertical dimensions,** secondarydimensionality shows number of **horizontal dimension**s.

Lets take our sample data.

We have four dimensions(columns):

Product,Category,Type and Sales.

Each Product have 2 Categories

Each Category can have up to 3 Types

Let's create Pivot Table using 3 of those D*imension* Product,Category and Type and use Sales as our *Expression*.

This Pivot Table has 3 dimensions so its maximum **dimensionality** is 3.

**'Sugar'** has dimensionality of **1** *which is Total for that 'Product'.*

**'Salt'** has dimensionality of **2*** which is Total for each 'Category' of that 'Product'.*

**'Oil'** has dimensionality of **3*** which is single value for each 'Type' of the 'Product's' 'Category'*

So in few words** ****:**** secondarydimensionality (and dimensionality) is the number of ****dimensions**** used in a Table**

For better understating please see table below:

The function is used to show on which dimensionality level each of the Pivot Table row is:

**Practical use:**

1) To show the level of dimensionality:

Expression:

if(secondaryDimensionality()=1,RGB(151,255,255),if(secondaryDimensionality()=2,RGB(0,238,0),

if(secondaryDimensionality()=3,RGB(255,130,171))))

2) Highlight background of rows which on each level fall into certain condition:

Expression:

*if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))*

LEVEL 1 --> Values <35 | LEVEL 2 --> Values <=15 | LEVEL 3 --> Values <=20 |
---|---|---|

- To use the script below the Excel file must be saved in the same folder that your qvd file

Otherwise you will need to make changes the this path - secondarydimensionality.xlsx

Directory;

LOAD Product,

Category,

Type,

Sales

FROM

secondarydimensionality.xlsx

(ooxml, embedded labels, table is Sheet1);

Still feeling hungry?

Do you Qualify?- How to use QUALIFY statement

Missing Manual - GetFieldSelections() + Bonus Example

MaxString & MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

## Comments