Visual column inside visual measures
We have covered the different types of manual input columns that can be inserted in the report in the previous sections. We have also learned the steps to insert calculated columns, in the form of visual measures as well as visual columns.
In this page, we will look at a specific case where you can include a data input visual column to be referenced inside a visual measure formula.
Data fields like interest rates, discount rates, tax slabs, and foreign exchange rates might be common to all the column dimensions. Hence they would be created as visual columns and we might need to refer to them often in calculations. In such cases, you can reference those visual columns in visual measures using the COLUMNS prefix.
Let us consider the following example: a visual input column is included to specify a discount rate of 10%.
To calculate the new sale price, we can insert a formula measure.
In the formula, we need to refer to this visual column, Discount% to calculate the discounted sale price. The visual column is referred to using the COLUMNS prefix identifier.
Click Create and the result is shown below:
In the current edition, visual measures can only refer to data input visual columns and not calculated visual columns (formula columns).
2.1.3. Insert rows from Look-up Tables
You can also insert rows and their leaf categories from Lookup Tables.
STEP 1: To pick and upload row categories from Lookup tables, navigate to 'Home' > 'Manage Rows' > 'Row Settings' > 'Insert Row Configuration' > 'Manage'.
In the Insert Row Configuration pop-up, select Options list from Lookup Tables from the Type dropdown.
STEP 2: The Add options from Semantic model window opens. In the Table Connection tab, select the workspace, semantic model and the table you need to connect to. Click Next.
STEP 3: Go to the Options configuration tab and choose the column from the connected table that will be used as the option label. In the example below, we have chosen the column 'Category' from the table 'Contoso-Product'. This column's members will be used to populate the 'Categories' row level. STEP 4: You can optionally specify a filter if hierarchical data is involved, and you want to maintain the same structure in your reports. In the Columns dropdown, select the field based on which you need to filter the options. In this case, it is the Contoso - Product.SubCategory which can be used to identify the Category. Select the matching field from the visual (SubCategory) in the Visual Column dropdown.
Then click Add.
Because of this filter, when you insert a row and add a sub-category first, you will be prompted with the category to which it belongs.
Configuring filter options is optional. By adding it, we ensure that the dropdown list for each category displays only the relevant sub-categories and vice-versa. For example, if you choose "Cellphones" as the category, only the sub-categories under "Cellphones" will be displayed, not other categories.
Similarly, when you choose a sub-category, you can add it only under the relevant category.
STEP 5: Follow the same steps from 1 to 4 for SubCategory dimension.
Once the configuration is completed as shown in the image above, you can follow the steps in sections 2.1/2.2 to insert the configured row categories into your visual.
You will see the data from the semantic model in the Category and Sub Category dropdowns.
The rows are inserted as shown below:
The base data in our visual may be from one table, but the options in the dropdown may be from a different table; in such cases, you can specify a join. For more information on using joins and filters, please refer to this section.
Was this helpful?