Filter Data Using Slicers

The Aimplan Data Input Table has great built-in data filtering and search functionality. This functionality is filtering data that has been loaded from the underlying storage table up to the browser into the visual.

But in some scenarios, it is not desirable to load and display all the records in a storage table to the end-user in the Data Input Table visual. Showing thousands and thousands of records that the end-user must scroll through is not very user-friendly, even with the built-in search functionality.

Another reason to limit the number of records is related to data access control. You may want to restrict end-users to accessing and editing specific records, such as modifying the chart of account structure for a particular company.

In Power BI, you can control data access using Row Level Security (RLS) and Dynamic RLS. These features determine the data that a user should have access to. By passing the appropriate filter parameters to the Data Input Table, you can effectively filter the data retrieved from the server.

The Aimplan Data Input Table supports adding filter parameters by sending in a concatenated string of filter values to the visual from Power BI. This means that it's possible to use one or two slicers and send the values from these slicers into the visual to filter the rows loaded from the storage table. This kind of filtering is performed on the server-side, and not in the browser.

Note: The objective of this kind of filtering is not to replace the search and filtering mechanism built into the visual. Instead, it should be used as a pre-filter to filter the data loaded to the browser. This kind of filter should be used on for example Company, Product Group, Account Groups, or similar dimensions. It should not be used to filtering individual products, accounts etc - here the built-in search and filter functionality should be used.

Consider the following example: We have added a normal Slicer at the top of the page where the end-user can select one or multiple companies. The available companies in this slicer can be controlled using RLS.

In the above example, the user has selected Swedish and UK-based companies. Now, we want to apply this filter to the Aimplan Data Input Table.

The Aimplan Data Input Table can accept one or multiple filter parameters or measures in the Filters field box. We have added a calculated measure to the Company dimension, which dynamically contains the Id/Key for the selected companies in the slicer.

Next, we added this calculated measure to the Filters field box in Power BI, renaming it to match the exact field name we want to filter in the table (e.g., "CompanyKey"). This ensures that the measure's data is sent to the Data Input Table, allowing the visual to select data from the underlying table that matches the selected CompanyKey(s) in the slicers.

You can provide one or more filter measures to the same Data Input Table. This means you can have multiple slicers or other Power BI visuals capable of filtering data to control what is displayed in the table. It's important to inject the measure with the same field name as the underlying table field you want to filter.

You might wonder about the structure of the filter parameters or measure DAX and the data it should provide to the Data Input Table. It should be a comma-separated list of values used for filtering. In the example, we have placed a 'card' visual at the top-right of the page, displaying the value of the 'SelectedCompanyKeys' measure. For instance, we selected two companies: CO1 (Sweden) and CO4 (UK), which are sent to the Data Input Table as a comma-separated list.

Here's an example DAX statement illustrating how to create such a measure:

In this measure, the ALLEXCEPT function removes the filter on the company name, ensuring that each row passed to the table contains all relevant items.