TM1 Forum

Posted: **Thu Jun 15, 2023 1:50 pm**

Hi,

I have an issue with a websheet in TM1-Web.
In the websheet I have a filter where users can choose any element from an account-dimension.
I used the SUBNM-formula without subset.

Problem is, users can pick elements resulting in an empty data-table.
I hoped, supressing zeros would fix it (which it didn't).

Is it achievable to only offer elements of a dimension that contain values for the data-table to avoid empty data-tables?

Posted: **Thu Jun 15, 2023 8:43 pm**

Hello,

Yes. Use a subset name in your SUBNM towards a public dynamic subset (calculated with an MDX statement).

For instance,

Code: Select all

Filter( TM1FilterByLevel( TM1SubsetAll( [BS_Account] ), 0), [BalanceSheet].([FIN_Year].[2022],[FIN_Period].[P07],[FIN_Scenario].[Actual],[BS_Measures].[Amount]) <> 0)

This would filter balance sheet accounts that have a non-zero balance in the balance sheet cube.
The values for other dimensions like year and period could come from Excel cells or other cube cells (like a system cube).

Posted: **Tue Jul 04, 2023 1:30 pm**

Hi Wim,

thanks for your answer and sorry I respond so late (was on vacation).
I didn't think of your solution and it works very well.

TM1 Forum

TM1Web Supress filters that lead to empty tables

TM1Web Supress filters that lead to empty tables

Re: TM1Web Supress filters that lead to empty tables

Re: TM1Web Supress filters that lead to empty tables