Excel Filtering Limit - only 32,000 values are allowed in the filter

Problem statement:

Sometimes we have very large dimensions in the Cubes and when we create Excel Reports using those dimensions in the Filter etc. We face the excel filter limit restriction, only 32,000 vales are allowed in the filter.

But still, we want to create the report and the report should work properly for the end-users.

Solution:

We can achieve this by creating the Groups where we can group the dimension values and create hierarchy using this Group description and dimension values.

Say suppose we have a dimension Employee. In the Employee Dimension, we have around 150,000 records. So then we create a Report using the Employee in the filter. The Excel Report will through an error when we select the values for the employee as the number of counts is more than 32000 for the Employee dimension.

So for this, we can create h new column in the Employee dimension which is Employee Group and bifurcate the values into these groups.

As the number of employees is 150,000.
So, we can create a group of 500 employees based on the Employee ID and there will be 150,000/500=300 groups created.

This group will be then used to create the hierarchy in the SSAS cubes.
By keeping the excel limitation (32,000) in the mind to group the values.

Employee Group-->Employee

So, now in the excel report, we can use the employee group hierarchy in the filter.
By selecting the particular group we can choose the employee which we want to select.

This will solve our issue of 32,000 values for the filter issue in Excel.