Lookup and Relation
Last updated
Last updated
EDITable allows you to configure any field in the table using the single-select input type. The lookup and relation field is specifically designed for setting up single-select type columns. In addition to manually configuring the dropdown options, EDITable offers an effective method for sourcing values from dimensions, even if they reside in a different table or data source, through the use of lookup tables.
You can also use the helper/lookup visuals to source values from a dimension. In summary, we have the following methods to configure the options:
Manual: Enter the dropdown values manually.
Distinct Values: Set up the existing options as a dropdown list with distinct values.
Lookup Table: Source values from another table in a database, which is usually done for foreign key fields.
Lookup Visual: Use lookup/helper visuals to source your values.
This section will provide a detailed procedure for configuring dropdown values for each method.
This method allows you to type in the dropdown options and labels manually.
Let us take an example where the Department ID field is set manually.
Choose Single Select in the Input Type field for the Department ID. As soon as you choose, the 'Lookup & Relation' section gets enabled.
Click on the link to configure.
Click on the Manual option.
Enter the options and labels as required.
Click on Add to add further new options.
Click on the bin icon beside an option, in case you want to delete it.
Click Apply and then Save the configuration.
You can now edit the table with the added dropdown options in the required field.
If you choose this option, EDITable creates a distinct list of options from the existing values. In the table below, the Incentive field already contains the following values: Bonus and Commission. These two values are available throughout the field.
When you choose Distinct, these values become the options for the dropdown.
EDITable lets you look up and source values from other tables.
When you click Lookup, the below pop-up opens where you can enter the name of the schema, table, label, and the options from where the dropdown options should be sourced.
Click Apply after entering all the details as shown below and then Save the configuration. EDITable fetches the values from the configured field in the table.
Now you can enter the configured dropdown values in the table.
Using the helper lookup visual is a more effective way to source values from other tables. It simplifies the process compared to the previous lookup method and ensures access to the most current data. It involves the following steps.
Step 1: Importing the helper/lookup visual into Power BI
Step 2: Configuring the dimension into the visual
Step 3: Configuring the lookup table within EDITable with the helper visual details.
The Inforiver Lookup visual works in tandem with EDITable (and Inforiver Matrix visuals). It can be downloaded for free from our customer portal and then imported into Power BI.
If you want to know the steps to import, you can refer to this link.
Configuring the visual is as simple as dragging the dimension you want to cross-reference into the Dimension field. Depending on the number of tables you want to source in your report, you can add multiple lookup visuals.
Enter the visual and the column name from which the options should be fetched. Click Apply and Save.
You can now use the values in the table.
To learn more about helper/lookup visuals, you can refer to this blog.
In the next section, we will look at the display settings.
