Creating a 'distinct' dropdown table

When configuring the Aimplan Data Input Table, you can use a 'Dynamic dropdown' to make the user select data from a drop-down.

To add data to this kind of dropdown, you add fields to the 'Fields' property of the Data Input Table. In the example below the two fields SubAccountGroupId and SubAccountGroup are used as Id and Text for a dropdown.



This all works good if you only select fields from one and the same table.

But say that you have four (4) dropdowns in a table, each containing 20 records. Power BI will create a cross-join and creating 20x20x20x20 = 160 000 records. This will cause the visual to be slow because it will take time to load all the data to the client. Power BI will also only deliver a maximum of 30 000 records.

This chapter is about how to solve this by adding a special calculated table to Power BI and adding distinct values. There are probably other alternative ways to solve the same issue, but here is an example of a solution.

Say that we we want to have two drop-downs, one where the end-user will select between two (2) products, and another where the user select between two (2) customers. Each source table contains a Key field and a Name field.

Power BI would if adding these four fields to the Data Input Visual create a cross join of these two tables and with a total of 2x2 = 4 records. This is no problem, this will not cause a huge data volume or any other problem. But we use this example to find a solution to the problem if you want to have many dropdowns, where each dropdown contains many distinct values.





Let's create a new calculated table named 'DropDownData'. As a starting point it will only contain one column that we can name AutoCounter, which is just adding a serie of numbers from 1 to a high number, maybe 100, or 1000 or similar. The number of records that should be generated should cover the maximum number of records you think you need in any of the drop-down. (I.e. if you have 10 customers and 90 products you should generate >90 records in this table)

The idea is to in the coming step add distinct values from both the product and customer table as new columns to this table.

For each distinct value records that you want to have in a drop-down, you in the source table add again add a RowId starting with 1. We do this for both the customer and product table.

Step 3 is about adding column by column records from the source tables (customer, product) to the DropDownData table using the 'RowIds' to not create a crossjoin. All columns in the DropDownData table is populated individually without any relations. But they all end up in the same table that can be used as the source for dropdowns.

So by using the AutoCounter Id in the DropDownTable, looking up values in each source table, you can create a new table perfect for adding to dropdowns in the Aimplan Data Input Table Visual.