Add Filter Context Manipulation Functions to Formulate, Similar to DAX

Hello PA-Team!

Description:

Currently, the Formulate module in the Pyramid BI system lacks functionality that allows flexible control over the filter context within calculations (formulas). Users need functions similar to the ALL, ALLEXCEPT, and ALLSELECTED functions from the DAX language (Power BI).

Justification:

Such functions are critically important for creating complex calculations that consider or ignore specific filters applied to the report. This enables:

• Comparing values with grand totals: Calculating shares and percentages of the grand total, ignoring filters on specific dimensions.

• Preserving the selection context: Analyzing data in the context of all user-selected values, but without the filtering effect of a specific hierarchy.

• Creating more flexible and powerful calculations: Expanding analytical capabilities and creating more complex reports.

Workaround Consideration:

We understand that a potential workaround might involve adding all columns of a table into the context of the Formulate node (data marker) in an attempt to bypass certain filters. However, this approach is highly inefficient, generating very large queries and placing a significant load on the system. Furthermore, it does not provide the necessary flexibility to replicate the specific behaviors of functions like ALLEXCEPT (removing all filters except specified ones) or ALLSELECTED (removing internal report filters while preserving external ones). The inadequacy and performance issues of this workaround further highlight the need for dedicated, efficient functions to manage filter context.

Requested Functionality:

We request adding functions to the Formulate module that provide the following behavior (similar to DAX):

1. ALL([<Table>|<Column1>[,<Column2>,…]]):

   • Removes all filters from the specified table or specified columns.

   • If a table/columns are not specified, removes filters from all tables and columns in the data model.

2. ALLEXCEPT(<Table>; <Column1>[,<Column2>,…]):

   • Removes all filters from the specified table, except for filters applied to the specified columns.

3. ALLSELECTED([<Table>|<Column1>[,<Column2>,…]]):

   • Removes filters from the specified table or specified columns that are applied inside the current visual element (report).

   • Preserves external filters (e.g., from slicers) and filters applied to other dimensions.

   • If a table/column is not specified, removes internal filters from all tables.

Thanks!