Inforiver
FormulaPricingProductAll Products
Reporting & Writeback Matrix Docs
Reporting & Writeback Matrix Docs
  • Introduction to Inforiver
    • Why Inforiver
    • Get Started
      • Installing Inforiver for yourself
      • Installing from AppSource
      • Installing Inforiver for your organization (Org visual)
      • Inforiver Writeback Matrix pre-requisites
        • Whitelist Inforiver IPs
        • Admin consent for Inforiver's Entra ID
          • Basic consent
          • Full admin consent
      • Inforiver Customer Portal
        • Billing Admin
          • Update Profile
          • Update Subscriptions
        • License Admin
      • Licensing
        • License renewal
      • Registering Inforiver on Azure AD (Entra ID)
  • Working with Inforiver
    • 1. Build your first Inforiver report
    • 2. Displaying information
      • Report interface
      • Layout options
      • Templates
      • Basic formatting
        • Number formatting
        • Cell, header & value formatting
        • Totals & subtotals
        • Insert blank rows
      • Basic inline charts
    • 3. Basic interactions
      • Explore & filter data
        • Search & Filter
        • Explorer
        • Top N + others
      • Sort & reorder data
      • Show/hide values
      • Manage column widths
      • Manage hierarchies
      • Create visual hierarchies
      • Pin rows & columns
      • Blend measures
      • Dynamic measure headers
      • Context menus
        • Column gripper
        • Row gripper
      • Actions
        • Rails
        • Toolbar options
        • Keyboard shortcuts
        • Reset
    • 4. Adding business logic and formulae
      • Insert calculated rows
        • Template rows
      • Insert calculated columns
      • Insert manual input rows
      • Insert manual input columns
        • Number
        • Dropdown
          • Dropdown options from semantic models
          • Lookup visuals for dropdowns
        • Text, checkbox & date
        • Writeback Matrix features
      • Invert sign
      • Editing cells
      • Quick formula
      • Visual column inside visual measures
      • Manage inserted rows
      • Manage inserted measures & columns
      • Pivot data
      • Insert image URLs
    • 6. Working with charts
      • Sparkline charts
      • Stacked charts
      • Bullet charts and clustered bar charts
      • Waterfall charts
      • Bar and pin charts
      • Change/deviation charts
    • 5. Conditional formatting
      • One-click options
      • Create rule
        • Rules (If conditions)
        • Color scale & data bars
        • Classification
        • Ranking
      • Manage rules
    • 7. Planning, budgeting & forecasting
      • What-if analysis & simulations
      • Goal Seek
      • Scenarios (Writeback Matrix only)
      • Budgeting & allocations
      • Forecasting
        • Date rollover and extending forecasts
        • Editing forecasts and reforecasting
        • Autoclose forecasts
        • Distribute deficit
        • Row level forecast
        • User Access Control
        • Common Scenarios
          • Forecast using a Blank Template
          • Forecast using Existing Data
          • Adding Forecasts for Periods unavailable in the model
          • Creating daily and weekly forecasts
      • Set version
    • 8. Paginated reporting
      • Pagination
      • Header & footer
        • Text
        • Images
        • Charts
        • KPI cards
      • Report layouts (Report+)
      • Display and themes
    • 9. Commenting and collaboration
      • Notes
      • Comments
        • Data-level comments
        • Assign & track tasks
        • Track changes
        • Report-level comments
        • Comment settings
    • 10. Exporting reports
      • Export to PDF & Excel
        • Excel export - operational boundaries
      • Ad-hoc emails
      • Snapshot (Writeback Matrix only)
        • Comparing snapshots
    • 11. Scheduling reports
      • Create new subscription
        • Select a report
        • Set subscription frequency
        • Select destination(s)
          • Email
          • OneDrive
          • Microsoft Teams
          • SharePoint
        • Review & save subscription
      • Manage your subscriptions
      • Administration
    • 12. Data writeback
      • Settings
        • General Settings
        • Data Settings
        • Destination Settings
        • Writeback security settings
        • Writeback time-out & batch size settings
        • Advanced Settings
      • Destinations
        • Azure SQL (Managed DB)
        • Azure SQL
        • Microsoft SQL Server
        • Synapse Dedicated SQL Pool
        • Azure Data Lake Storage
        • Databricks
        • Snowflake
          • Setting up Snowflake OAuth
        • Amazon Redshift
        • BigQuery
        • SingleStore
        • SAP HANA
        • Oracle
        • PostgreSQL
        • MySQL
        • OneDrive
        • SharePoint
        • Dataverse
        • Fabric Warehouse
        • Fabric lakehouse
        • Fabric SQL
        • URL as destination
      • Logs
      • Reading mode
    • 13. Context awareness
    • 14. Manage aggregation
      • Row aggregation
      • Column aggregation
    • 15. Managing Inforiver Writeback Matrix visuals
      • Inforiver deployments in Power BI pipeline setup
      • Duplicating Writeback Matrix Visuals - Best Practices
      • Recommended practices while working with Power BI desktop
    • 16. Approval workflows
    • 17. Collaborative adjustment workflows
    • 18. Variables
      • Variable types
        • Number
        • Select
        • Text
        • Date
        • Date range
        • Boolean
        • Button
      • Variable bookmarks
    • 19. Leveraging Power BI bookmarks
  • Admin Console
    • Manage Profile
    • User management
      • User Roles and Permissions
      • Create custom roles and policies
    • Settings
      • General
      • Generate builds - on prem
      • Forecast
      • Holidays
      • API token
      • Writeback
      • Managed DB
      • Assets
    • Onboarding actions
    • Writeback
  • Display settings
    • General settings
    • Hierarchy settings
    • Number settings
    • Miscellaneous settings
    • On-load scripting
  • Formula syntax
    • Conditional statements
      • IF
      • IFNA
      • SWITCH
      • FILTERIF
    • Logical functions
      • AND
      • IN
      • ISBLANK
      • ISEMPTY
      • ISNUMBER
      • NOT
      • OR
      • XOR
    • Math functions
      • ABS
      • AVERAGE
      • AVERAGEEXNEG
      • AVERAGEEXZERO
      • AVERAGEEXZERONEG
      • AVERAGEIF
      • CEILING
      • COUNT
      • COUNTIF
      • DIVIDE
      • EVEN
      • EXP
      • FLOOR
      • INDEXOF
      • LOG
      • ODD
      • MAX
      • MIN
      • NORMDIST
      • NORMSDIST
      • NORMINV
      • NORMSINV
      • PCT
      • POWER
      • RAND
      • RANDBETWEEN
      • ROUND
      • SORT
      • SQRT
      • SUM
      • PMT
    • Text formatting functions
      • CONCATENATE
      • HYPERLINK
      • LEFT
      • LOWER
      • MID
      • PROPER
      • REPLACE
      • REPT
      • RIGHT
      • TEXT
      • TRIM
      • UPPER
      • VALUE
    • Date functions
      • DATEADD
      • DATEDIFF
      • DAY
      • DATE
      • DATE.SET
      • DATE.FORMAT
      • SOWEEK
      • SOMONTH
      • SOQTR
      • SOYEAR
      • EOWEEK
      • EOMONTH
      • EOQTR
      • EOYEAR
      • NETWORKDAYS
      • ADDDAYS
      • ADDWEEKS
      • ADDMONTHS
      • ADDQTRS
      • ADDYEARS
      • FROMEXCELDATE
      • MONTH
      • NOW
      • PERIOD_RANGE
      • TODAY
      • TOEXCELDATE
      • YEAR
    • Time intelligence functions
      • AGGREGATE
      • FILTER
      • COLUMN_PERIOD
      • LASTNDAY
      • LASTNMONTH
      • LASTNQTR
      • LASTNYEAR
      • MONTHPERIOD
      • MOVINGAVERAGE
      • MOVINGSUM
      • MTD
      • NEXTNDAY
      • NEXTNMONTH
      • NEXTNQTR
      • NEXTNYEAR
      • QTD
      • QTRPERIOD
      • SHIFT
      • TOTALYTD
      • TOTALQTD
      • TOTALMTD
      • VALUEAT
      • YEARPERIOD
      • YTD
    • Other operators
    • Identifiers
      • CLOSEDPERIOD
      • COLUMN.DATE
      • COLUMN.PARENT
      • COLUMNS
      • CURRENT_PERIOD
      • DESCENDANTS
      • FORECAST.CLOSED_END
      • FORECAST.CLOSED_START
      • FORECAST.OPEN_END
      • FORECAST.OPEN_START
      • GROUP INDEX
      • HAS
      • HAS_ALL
      • HAS_SOME
      • LEAVES
      • LEVEL
      • LOGGEDIN_EMAIL
      • LOGGEDIN_NAME
      • MATCH
      • MAXDATE
      • MEMBERS
      • MINDATE
      • RELATIVE
      • RELATIVE_COLUMN
      • ROW.PARENT
      • ROW.LABEL
    • Scripting functions
      • DISPLAYTOAST
      • EXPORT
      • LAYOUT
      • SETCFRULE
      • SETFILTER
      • SETFILTERBYINDEX
      • SETVISIBILITY
      • SHOWBETWEENPERIOD
      • SHOWCLOSEDFORECAST
      • TOPN
      • DISPLAY SETTINGS
        • SETBLANKLABEL
        • SETSUPPRESSZEROS
        • SETRAGGEDHIERARCHY
        • SEPARATOR
        • SETCOMBINECOLUMNHEADER
        • SEMANTICFORMAT
        • SHOWNULLAS
        • SHOWZEROAS
        • SINGLECHILD
    • Miscellaneous functions
      • FETCH
      • LOOKUP
    • Measure/Column selection functions
      • GETCOLBETWEENPERIODS
      • GETMEASURE
  • Advanced topics
    • Audit log
    • Backup & restore report config
    • Enterprise themes
    • PowerPoint integration
    • Drill down and roll up in performance mode
  • Infobridge
    • 1. Create bridge
    • 2. Add source to bridge
    • 3. Bridge Interface
    • 4. Manage queries
      • Query transformations
        • Append
        • Join
        • Duplicate
    • 4. Manage sources
    • 5. Insert rows from bridge
    • 6. Insert measure from bridge
    • 7. Manage Integration of visuals
    • 8. Manage Bridges
    • 9. Data transformations
      • Pivot table
      • Group by
      • Add Column
      • Pivot column
      • Rename column
      • Find and Replace
      • Remove measure
      • Filter rows
      • Sort
      • Text functions
      • Unnest
      • Round
      • Extract Comments
      • Import and export
    • 10. Integrating forecasts
    • 11. Collaborative adjustments
  • Accessibility shortcut keys
  • Inforiver Reporting Matrix FAQs
  • Inforiver Writeback Matrix FAQs
  • Video tutorials
  • Resources
  • Release Notes
    • Inforiver Writeback Matrix May 2025 - v4.4.3
    • Inforiver Writeback Matrix April 2025 - v4.4.2
    • Inforiver Writeback Matrix April 2025 - v4.4.
    • Inforiver Writeback Matrix March 2025 - v4.3 - Time series formulae
    • Inforiver Writeback Matrix - March 2025 - v4.2
    • Inforiver Writeback Matrix March 2025 - v4.1
    • Inforiver Reporting Matrix March 2025 - V3.3.3 -New formatting and export options
    • Inforiver Writeback Matrix Feb 2025 - v4.0 - User permissions, High-performance mode, Input rules
    • Inforiver Writeback Matrix January 2025 - v3.9 - Fabric support, upgraded variables interface
    • Inforiver Writeback Matrix December 2024- v3.8 - Improved Scheduler Performance & Other Enhancements
    • Inforiver Writeback Matrix December 2024 - v3.7 - Merge rows, new rounding functions & More
    • Inforiver Writeback Matrix November 2024 - v3.6.2 - Import from Google Sheets & More
    • Inforiver Writeback Matrix October 2024 - v3.6 - Approval Workflows & Other Enhancements
    • Inforiver Writeback Matrix October 2024 - v3.5 - Lookup visuals & more
    • Inforiver Reporting Matrix Nov 2024 - v3.3 - Variables
Powered by GitBook
On this page
  • 1. Prerequisites
  • 2. Creating a LOV from a semantic model
  • 3. Using joins and filters
  • 4. Refreshing data

Was this helpful?

  1. Working with Inforiver
  2. 4. Adding business logic and formulae
  3. Insert manual input columns
  4. Dropdown

Dropdown options from semantic models

PreviousDropdownNextLookup visuals for dropdowns

Last updated 8 months ago

Was this helpful?

For the Single select and Multi-select columns, a list of values (LOV) can be created from your Power BI semantic models or other dimensions like Master Data reference fields. The options are dynamically updated as the source data changes.

1. Prerequisites

In order to source dropdown options from published semantic models, users need to be able to query the semantic models using Power BI REST APIs. In the Power BI admin portal, enable the Semantic Model Execute Queries REST API toggle. Choose the Apply to - The entire organization radio button.

2. Creating a LOV from a semantic model

Let's create a LOV from the dataset for a multi-select column. We'll create a multi-select field to assign a subregion.

STEP 1: In the multi-select column side panel, click on Semantic model to open the Add options from semantic model dialog box.

STEP 2: Select the Power BI workspace from the 'Workspace' dropdown. You can also search for a specific workspace.

STEP 3: Select the semantic model and the table from the respective dropdowns. Click Next.

STEP 4: From the Label Column dropdown, you can choose the column from the table (selected in Step 3) which will be used to populate the multi-select options.

You can associate the options with an ID by selecting the ID Column. For example, if the multi-select shows a list of product names, you can tag them to a product ID field (if available in the data model). The multi-select options will not be impacted even if the product name changes, as the Product ID field remains constant.

If an ID field is not available in your dataset, use the Label Column as the ID column.

You can assign regions from the multi-select dropdown after completing the steps discussed above.

3. Using joins and filters

Joins

The base data in our visual may be from one table, but the options in the dropdown can be from a different table. The join option comes in handy in these scenarios. If you need to join your base table with another table to source dropdown options, you need to be mindful that a common column connects the two tables. In this case, the Retail-Orders and Retail-People tables are connected by Region.

Filters

There are many scenarios wherein the options in the dropdown need to change based on the row dimension category. For example, consider that we have regional data in our rows. We need to use a dropdown to assign a manager for each region. The person we assign must also be tagged to that specific region i.e. the dropdown options for the Central region should only show the people under that region. We can use filters in such scenarios.

Let's look at a scenario where we need to use joins and filters. Please note that the filter and join features can be used independently as well.

STEP 1: Select the workspace, semantic model, and table as discussed in the earlier section.

STEP 2: You can fetch the data from another table by checking the Join Table checkbox. After ticking the checkbox, you will be able to specify the table to join with.

The base table used to populate rows and columns in the report is Retail - Orders. We need to fetch the person data from the Retail - People table. The Retail -Orders and Retail-People are connected by the Region field (foreign key).

STEP 3: Let's assign the field to source the multi-select options - in this case, Retail - People.Person. Since the Retail - People table does not have a unique ID column for each person, let's use the Person field as the option and the ID.

STEP 4: We need to filter the people based on the region they are assigned to. In the Columns dropdown, select the field based on which you need to filter the options. In this case, it is the Retail - People.Region which can be used to identify the people tagged to a region. Select the matching field from the visual from the Visual Column dropdown.

The dropdown options for people data have now been fetched from the Retail - People table. Notice how all the people are displayed in the options for the grand total row. For each region, only the person assigned to that region is shown in the dropdown.

Data validation check for copy-paste in dropdowns

When dropdown filters are enabled, Inforiver also checks if the data is valid when you copy-paste data manually within a dropdown data input column. Note that the Copy options available in the report (Copy to all rows, Copy until last row etc.) are disabled when dropdown filters are applied. If you accidentally try to paste an invalid option, you will be notified that the copied cell values do not belong to the chosen category.

In the following example, we tried to copy the dropdown cell values (Bookcases, Binders) from the categories Furniture and Office Supplies, and paste them into rows belonging to different categories (Bookcases -> Office Supplies and Binders -> Technology). Inforiver prevents this type of copy-pasting, as the data is not valid for the respective categories. You will see a toast message indicating that the operation is not allowed.

4. Refreshing data

The underlying data in the semantic model is likely to change - there may be new data added or updates to the existing data. Any updates to the underlying data should be reflected in the dropdown options too.

  • You can schedule an automatic refresh at a specific time on a daily/monthly basis.

  • You can also run ad-hoc data refreshes to sync the dropdown options with the semantic model.

Triggering ad-hoc refreshes

STEP 5: After configuring and creating the dropdown, to preview all the options sourced from the semantic model, click the icon. If you need to change the configuration, click the icon to open the semantic model configuration window.

You need to check the Require Semantic model Refresh option to trigger manual refreshes. Click the icon in the Options section to trigger a refresh from within the Inforiver visual.

Inforiver also provides an API endpoint to manually sync the semantic model from outside the visual. The API endpoint is provided in the Refresh Link section.

Click on the icon to view the semantic model refresh history. Any errors that occur during the refresh can be viewed from this screen.

Learn more about working with API endpoints and authorization tokens.
Power BI Admin portal settings
Semantic model option
Select the workspace
Select the semantic model
Select the table - Acme Region Master
Selecting the column to source the multi-select options
Multi-select dropdown sourced from the dataset
Previewing options
Joining tables
Joining Retail-Orders with Retail-People
Soucing options from the Person in the dataset
Setting a filter for dropdown options
Filtering options dynamically
Data validation check in dropdowns with filters
Refresh dataset option
Manual refreshes
API refresh option
Refresh history portal