Implement and Manage Semantic Models practice questions

Flatten all dimension and fact data into a single wide table to eliminate all joins.

Why wrong: While flattening data into a single table eliminates joins, it often creates massive redundancy and significantly increases the memory footprint of the model. This approach can lead to inefficient column compression and may exceed memory limits, potentially causing the model to fallback from Direct Lake to DirectQuery mode during query processing.

Implement a snowflake schema to ensure that data normalization reduces the overall storage size.

Why wrong: Snowflake schemas introduce multiple levels of relationships between dimension tables, which increases the overhead for the Direct Lake engine when resolving joins. This often leads to sub-optimal query plans and slower visual rendering compared to a flattened star schema where dimensions connect directly to fact tables, requiring more compute resources.

Use many-to-many relationships between all tables to provide maximum flexibility for users.

Why wrong: Many-to-many relationships are computationally expensive and can cause ambiguity in data aggregation. In a Direct Lake environment, relying on these relationships increases the likelihood of performance bottlenecks and can trigger a fallback to DirectQuery, as the engine struggles to efficiently resolve the non-unique join paths and maintain the required speed.

Calculation groups automatically enable the implicit measure feature for all columns in the model.

Why wrong: The presence of calculation groups requires that the 'Discourage Implicit Measures' property be set to true. This means that users cannot simply drag a numeric column into a visual to sum it; they must use explicitly defined DAX measures, which ensures that the calculation group logic can be applied consistently.

Calculation groups are only supported in models that use the DirectQuery storage mode.

Why wrong: Calculation groups are supported across all storage modes, including Import, DirectQuery, and Direct Lake. Their primary function is to transform DAX measures at query time, regardless of how the underlying data is stored, making them a versatile tool for enhancing any semantic model's capability without duplicating logic.

Each calculation group can only contain a maximum of ten calculation items for performance reasons.

Why wrong: There is no hard limit of ten items per calculation group. While performance can be impacted by an excessive number of complex calculation items, the engine can handle many more than ten. Developers should focus on the complexity of the DAX logic rather than a specific count of items.

The entire 'Orders' table will be refreshed, including all historical partitions.

Why wrong: The script includes an 'objects' array that specifically identifies a single partition. While the table is mentioned, the scope is restricted to the partition level. Therefore, the engine will ignore other partitions in the same table, focusing only on the one defined in the JSON payload provided.

The command will fail because TMSL is not supported for Microsoft Fabric semantic models.

Why wrong: Microsoft Fabric fully supports the XMLA endpoint for Premium-level workspaces, which allows for the execution of TMSL and TOM commands. This support is crucial for professional BI developers who need to automate model management, partition refreshing, and metadata deployments using tools like PowerShell or Tabular Editor.

Only the metadata for the 'Orders' table will be updated without reloading data.

Why wrong: A 'type' of 'full' indicates a data refresh, not just a metadata update. If the goal were to only update the metadata or calculate dependencies, a different refresh type such as 'calculate' or 'defragment' would be used instead of the 'full' processing command shown in the exhibit.

A calculated column that determines the age of each record in days.

Why wrong: Incremental refresh relies on parameters applied at the data source level via Power Query, not on calculated columns. Calculated columns are processed after the data is loaded into the model, so they cannot be used to define the boundaries of the partitions during the ingestion phase.

The 'Enable DirectQuery' checkbox in the table properties.

Why wrong: Incremental refresh is primarily a feature of Import mode models. While it can be used with hybrid tables that include a DirectQuery partition, enabling DirectQuery for the entire table is not a requirement for standard incremental refresh and changes the fundamental way data is accessed.

Create a separate visual for each measure and use bookmarks to switch between them.

Why wrong: Using bookmarks requires creating and maintaining multiple visuals and buttons. This increases the complexity of the report and makes it harder to update in the future. Field parameters provide a much cleaner and more maintainable solution by using a single visual and a slicer.

Use the SWITCH function in a new DAX measure to select between the two calculations.

Why wrong: While a SWITCH measure can work, it often requires complex DAX to handle different formats (like currency vs. percentage). Field parameters handle this more gracefully by preserving the original formatting of each measure and offering a more intuitive interface for report authors and end-users alike.

Configure a drill-through action that navigates to different pages based on the measure.

Why wrong: Drill-through is intended for moving from a high-level summary to detailed data, not for switching the primary measure of a chart. It would be a confusing user experience for simply changing a metric and would require unnecessary page navigation for the end-user.

It defines a new calculated table that will be permanently added to the semantic model.

Why wrong: The EVALUATE statement in a DAX query is for execution and testing only; it does not persist a table into the model's schema. To add a permanent table, you would need to use the 'New Table' feature in the modeling tab and provide a DAX expression.

It triggers the creation of a relationship between the 'Date' and 'Sales' tables.

Why wrong: EVALUATE is used for data retrieval and result set generation. It does not modify the model's metadata or create relationships. Relationships must be defined in the Model View of Power BI Desktop or through the Tabular Object Model (TOM) via the XMLA endpoint.

It calculates the total sum of the 'Sales' column for the entire model.

Why wrong: While the query calculates sales per month, the EVALUATE statement itself is not a calculation function. It is a query instruction. The actual calculation of sums is performed by the SUMMARIZECOLUMNS and [Total Sales] measure logic defined within the query's variables and expressions.

Ensure that all relationships are set to 'Bi-directional' cross-filter direction.

Why wrong: Bi-directional relationships should be used sparingly as they can cause performance issues and ambiguity in the model. They force the engine to check for filters in both directions, which increases query complexity and can lead to unexpected results if multiple paths exist between tables.

Create multiple active relationships between two tables to provide different filter paths.

Why wrong: A semantic model can only have one active relationship between two tables at a time. Multiple paths can be defined using inactive relationships, which must then be explicitly activated in DAX using the USERELATIONSHIP function, preventing ambiguity in how filters propagate through the model.

Always use 'Many-to-Many' relationships to avoid data quality errors.

Why wrong: Many-to-many relationships should only be used when the data truly requires it. They are less performant than one-to-many relationships and can hide underlying data quality issues. It is better to clean the data and use standard relationships whenever possible to ensure accuracy and speed.

Row-Level Security (RLS) with a filter on the sensitive column.

Why wrong: RLS is designed to filter rows based on values, not to hide the structure of the model. If you use RLS, the column itself would still be visible in the field list, even if it returned no data for certain users, which violates the requirement for total invisibility.

Sensitivity labels applied at the workspace level.

Why wrong: Sensitivity labels are used for classification and protection (like encryption) of data, but they do not dynamically hide specific columns within a model based on the user's identity. They are more about data governance and preventing data leakage after the data is exported.

Workspace roles with 'Viewer' permissions only.

Why wrong: Workspace roles manage access to the entire item (the semantic model), not individual components within it. A user with 'Viewer' access can see all data in the model unless RLS or OLS is applied to restrict their view of specific rows or columns.

Enable the 'Auto-scale' feature in the Lakehouse settings.

Why wrong: Auto-scale in Fabric generally refers to capacity compute resources, not the specific storage format of a semantic model. While it helps with overall performance, it doesn't solve the specific limitations related to the size of an individual Power BI semantic model file.

Convert all tables to use the 'Import' mode with 'Basic' compression.

Why wrong: Import mode is standard, but 'Basic' compression is not a configurable setting that solves size issues. Power BI already uses high-level VertiPaq compression. For very large models, the focus should be on the storage format and partition management rather than changing the compression algorithm.

Use the Power BI .PBIX file format for all deployments.

Why wrong: The .PBIX format has limitations for very large models, especially during upload. Professional developers often use the .PBIP (Project) format or deploy via the XMLA endpoint to bypass the limitations of uploading a single large file through the Power BI Desktop interface.

The local Excel file must be uploaded to OneLake before it can be used in a composite model.

Why wrong: Composite models allow you to combine cloud-based semantic models with local data files directly in Power BI Desktop. While moving data to OneLake is a good practice for Fabric, it is not a technical requirement for creating a composite model with a local Excel file.

You cannot use Row-Level Security (RLS) in a composite model that includes DirectQuery.

Why wrong: RLS is still supported. If the source 'Corporate Sales' model has RLS, it will still be applied to the data retrieved from that source. You can also define new RLS rules in the composite model, although they will only apply to the data within that specific model's scope.

All tables from the 'Corporate Sales' model must be imported into the local model.

Why wrong: The primary benefit of a composite model is that you do not have to import the data. By using DirectQuery to the source model, you keep the data in its original location, which saves memory and avoids duplicating large volumes of enterprise data in your local file.

Use the RELATED() function to pull the 'ShipDate' into the 'Sales' table.

Why wrong: The RELATED() function is used to retrieve a value from the 'one' side of a relationship into the 'many' side. It follows the active relationship. It cannot be used to activate an inactive relationship or change the context of how a measure aggregates data across different date keys.

Use the TREATAS() function to map the 'ShipDate' to the 'Date' table.

Why wrong: TREATAS() is used for 'virtual' relationships where no physical relationship exists in the model metadata. While it could technically achieve a similar result, USERELATIONSHIP is the preferred and more performant method when a physical (but inactive) relationship has already been defined in the model.

Change the 'ShipDate' relationship to 'Bi-directional' in the Model View.

Why wrong: Making a relationship bi-directional does not make it active. A relationship must be 'Active' for the engine to use it by default. If you already have an active relationship on 'OrderDate', you cannot make 'ShipDate' active without deactivating the first one, regardless of the cross-filter direction.

The changes are automatically synced to Azure DevOps in real-time.

Why wrong: Git integration in Fabric is not automatic or real-time. This is a safety feature that allows developers to test their changes in the workspace before deciding to commit them. A manual action is required to move the changes from the 'uncommitted' state to the remote repository.

You must download the .PBIX file and manually upload it to the Git repository.

Why wrong: The purpose of Git integration is to avoid manual file uploads. Fabric uses the Power BI Project (.PBIP) structure internally for Git, which breaks the model into human-readable JSON files. This allows for better versioning and diffing than a binary .PBIX file would provide.

Run a 'git push' command from a local terminal connected to the workspace URL.

Why wrong: You cannot run standard Git commands directly against a Fabric workspace URL. While you can clone the Azure DevOps repository locally to work with files, the synchronization from the Fabric workspace itself must be initiated through the Fabric web interface's Source Control features.

Change the 'Data Category' of the measures to 'Group'.

Why wrong: The 'Data Category' property is used to identify the type of data a column contains, such as an Image URL, a Country, or a Postal Code. It helps Power BI understand how to visualize the data (e.g., on a map) and is not used for organizing fields into folders.

Use the 'Group' feature in the Report View to cluster the measures.

Why wrong: The 'Group' feature in the Report View is used to combine data points or categories within a visual (like grouping 'Small' and 'Medium' into 'Other'). It is not a metadata management tool for organizing the field list or the semantic model's structure for other authors.

Create a new table for each category and move the measures into them.

Why wrong: While 'Measure Tables' are a common practice, creating a separate physical table for every single category can clutter the model's diagram. Using 'Display Folders' within a single measure table is a cleaner and more standard way to organize large volumes of calculations without adding extra tables.

The Delta table has not been optimized using the V-Order algorithm.

Why wrong: V-Order is a write-time optimization that improves read performance for Fabric engines but does not trigger a fallback to DirectQuery. While unoptimized files might lead to slower performance within Direct Lake, the engine will still attempt to use Direct Lake mode unless a specific architectural limitation or security constraint is met.

The Delta table contains more than 100 columns.

Why wrong: Column count itself does not trigger a fallback to DirectQuery, although it may impact overall performance and memory consumption. Direct Lake can handle wide tables efficiently as long as the memory limits of the capacity are not exceeded and no complex model features like RLS or OLS are active on the dataset.

The data is stored in a different workspace than the semantic model.

Why wrong: Fabric supports cross-workspace data access, and having the Delta table in a different workspace does not prevent the use of Direct Lake mode. As long as the identity accessing the data has the appropriate permissions and the storage is within OneLake, the Direct Lake connection remains functional and performant.

It deletes all data in the Orders table except for the 2023 partition.

Why wrong: The refresh command with a type of 'full' is designed to reload data into the specified object, not to delete other partitions. In TMSL, deletion requires a separate 'delete' command or a 'sequence' of operations. This script simply ensures the 2023 data is updated while leaving other partitions in their current state.

It recalculates all measures in the model based on the 2023 data only.

Why wrong: While a refresh can trigger recalculations, this script is focused on data ingestion for a specific partition. Recalculation of measures usually occurs at the model or table level during a 'calculate' refresh type. This operation ensures the underlying data for the 2023 partition is current but does not restrict measure logic.

It updates the metadata for the Orders table without loading any new data.

Why wrong: Updating metadata without loading data is typically handled by the 'clearValues' or 'calculate' refresh types in TMSL. A 'full' refresh type explicitly instructs the engine to fetch and load data from the data source into the specified partition, ensuring that the actual records are updated within the semantic model's storage.

Power Query Editor

Why wrong: While Power Query Editor is used for data transformation and cleansing, it does not provide the telemetry needed to analyze DAX query execution or VertiPaq compression. It is a front-end tool for the mashup engine rather than a performance profiler for the semantic model's storage and query execution layers.

Azure Data Factory

Why wrong: Azure Data Factory is an orchestration and integration tool for data movement and transformation. It does not have the capability to profile DAX queries or analyze the internal performance metrics of a Fabric semantic model. Its role is strictly for ETL and pipeline management rather than model-level performance optimization.

Microsoft Purview

Why wrong: Microsoft Purview is used for data governance, lineage, and cataloging across the enterprise. While it can track the lineage of a semantic model back to its source, it lacks the technical performance profiling capabilities required to troubleshoot slow DAX queries or optimize VertiPaq engine storage and relationship processing.

Directly modifying the DAX formula of a measure.

Why wrong: The standard Power BI REST API does not support direct modification of individual DAX formulas within a model. To change DAX logic programmatically, one would typically use the XMLA endpoint with TMSL or TOM (Tabular Object Model), as the REST API is designed for higher-level management tasks rather than granular model metadata editing.

Creating a new Row-Level Security role from a JSON file.

Why wrong: Creating or modifying RLS roles is a metadata-level change that is handled through the Tabular Object Model (TOM) via the XMLA endpoint, not the standard REST API. The REST API manages the lifecycle and environment settings of the model, but the internal structure of the model itself is governed by TMSL/TOM.

Row-Level Security (RLS) with a dynamic filter.

Why wrong: RLS is designed to filter rows of data based on user identity, but it cannot hide the structure of the model. Even with RLS, the 'Unit Cost' column remains visible in the field list, although the values within it might be filtered. OLS is required to hide the column metadata entirely.

Calculation groups with a security measure.

Why wrong: Calculation groups are used to apply transformations to measures and do not provide a native way to secure columns or tables. While they can be used to return blank values based on security logic, they do not hide the underlying metadata, which is the primary requirement for this specific scenario.

A separate perspective for non-finance users.

Why wrong: Perspectives are used to simplify the view of a model by showing only a subset of tables and columns. However, perspectives are not a security feature; users can still access the hidden columns through other tools or by connecting directly to the model. They are for usability, not data protection.

Power BI Desktop's standard Model view.

Why wrong: The standard Power BI Desktop Model view allows for relationship management and basic property configuration but does not currently support the creation or complex editing of Calculation Groups. To implement these, developers typically rely on the XMLA endpoint to enable external modeling tools that provide deeper access.

Microsoft Fabric Data Factory.

Why wrong: Data Factory is used for data ingestion and orchestration of pipelines. It does not have any features for modifying the internal structure of a semantic model, such as creating calculation groups or defining DAX measures. Its role is strictly at the data movement and transformation layer within the Fabric ecosystem.

The Fabric Lakehouse Explorer.

Why wrong: The Lakehouse Explorer is used for browsing files and tables in OneLake and managing the Delta lake schema. It does not provide modeling capabilities for semantic models, such as creating calculation groups, which reside in the Analysis Services engine layer rather than the physical storage layer of the Lakehouse.

Include high-cardinality columns like GUIDs in the fact table.

Why wrong: High-cardinality columns, such as unique identifiers or timestamps with millisecond precision, are detrimental to VertiPaq performance. They prevent efficient compression and consume significant memory. Engineers should remove these columns or reduce their granularity to improve the model's footprint and speed up query response times.

Create many-to-many relationships for all dimensions.

Why wrong: Many-to-many relationships are computationally expensive and can lead to unexpected results if not managed carefully. They should only be used when the underlying data truly requires it. Overusing them instead of standard one-to-many relationships will result in slower queries and increased complexity in DAX measure logic.

Flatten all dimensions into a single large fact table.

Why wrong: While 'flat' tables can sometimes be fast for specific queries, they generally lead to massive data redundancy and poor compression in VertiPaq. This approach negates the benefits of the star schema, making the model harder to manage and often increasing the overall memory usage of the semantic model.