Keeping the detailed Impressions fact table but restricting it to the last 12 months preserves the required grain while a star-schema one-to-many relationship to the Date, Ad, Campaign, and Site dimension tables (B) enables slicing by day, campaign, and Sitename without duplicating data.

Row counts are best returned through an explicit DAX measure that uses COUNTROWS (C); a measure occupies only the formula cache, so it adds virtually nothing to the in-memory model size, meeting the minimisation requirement. Creating additional aggregated or calculated tables would increase memory use and are therefore avoided.

A Grouping into a separate query/table adds another fact table; even if smaller than the raw data, it is still additional storage and not required when a measure can aggregate on demand.

D A calculated table is materialised in the VertiPaq store, duplicating columns already present and therefore increasing, not reducing, model size.

1. Microsoft Docs – “Designing a star schema in Power BI”: Relationships should be one-to-many between dimension and fact tables. (Power BI guidance

p. 3

section “Star schema overview”)

2. Microsoft Docs – “Data reduction techniques for import models”: Use measures instead of calculated tables/columns to minimize model size. (p. 2

paragraph “Prefer measures over calculated columns”)

3. Microsoft Docs – DAX Function Reference: COUNTROWS returns the number of rows in a table; used in measures without increasing model storage. (COUNTROWS article

Examples section)

4. Russo

Ferrari. “Optimizing DAX Models.” SQLBI White-paper

2020

§2.1 “Memory footprint of measures vs. tables.”

For row-level security (RLS) to function correctly across a bidirectional relationship, two conditions must be met. First, the relationship must be active to allow filters to propagate between the tables by default. Second, the "Apply security filter in both directions" option must be enabled for that relationship. This specific setting ensures that the RLS filter applied to one table is also propagated to the related table, enforcing the security rules regardless of which table is being filtered first. This is crucial when both tables in the relationship have RLS rules defined.

A. Assume referential integrity is a performance optimization setting for relationships; it does not control the direction or application of security filters.

B. An inactive relationship does not propagate filters unless explicitly invoked by a DAX function, and Assume referential integrity is irrelevant to RLS.

C. An inactive relationship prevents automatic filter propagation, which contradicts the requirement for standard bidirectional cross-filtering to work with RLS.

1. Microsoft Learn. (2023). Row-level security (RLS) with Power BI.

Section: RLS with bi-directional cross-filtering

Content: "By default

RLS filtering uses single-directional filters

whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with RLS by selecting the relationship and checking the Apply security filter in both directions checkbox." This directly supports enabling the specific setting on an active relationship.

2. Microsoft Learn. (2023). Model relationships in Power BI Desktop.

Section: Relationship properties

Content: The documentation describes the "Apply security filter in both directions" property

explaining that when enabled for a relationship with a "Both" cross-filter direction

it propagates the security context to the related table. This is the exact mechanism required by the question.

3. Microsoft Learn. (2023). Bidirectional cross-filtering.

Section: Row-level security

Content: "When a DAX expression for a row-level security (RLS) rule is active

it will also filter in the other direction. ... You must enable the Apply security filter in both directions property for the relationship." This confirms that for RLS to work bidirectionally

this property is mandatory.

The stated goal is to analyze events by the hour and the day of the year, while also improving dataset performance. Changing the IOT DateTime column's data type to Date removes the time component entirely. While this action reduces the column's cardinality and would improve performance, it makes the required hourly analysis impossible. Because the solution fails to meet a critical part of the analytical requirement, it does not meet the overall goal.

A. Yes: This is incorrect. The solution prevents analysis by the hour because converting the column to a Date data type discards all time-related information.

1. Microsoft Learn. (2023). Data types in Power BI Desktop. "Date - Represents just a Date (no time portion)." This official documentation confirms that the Date data type does not store time information

making hourly analysis impossible after the proposed conversion. Retrieved from https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

2. Microsoft Learn. (2023). Optimization guide for Power BI. Under the section "Data model optimization techniques

" the guidance discusses reducing column cardinality to improve performance. It states

"The VertiPaq storage engine...achieves better compression for columns with lower cardinality." A DateTime column has very high cardinality. Converting it to Date reduces cardinality

which addresses the performance goal

but it fails the analytical goal. Retrieved from https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization#reduce-column-cardinality

3. Microsoft Learn. (2023). Add a date or time column in Power Query. This document shows the correct procedure for this scenario

which involves adding new columns for specific date or time parts (like Hour) from an existing DateTime column

rather than changing the data type of the source column and losing information. Retrieved from https://learn.microsoft.com/en-us/power-query/add-date-time-column

Changing the cross filter direction can improve report query performance by optimizing the flow of filters through the data model. In a typical star schema, filters flow from the dimension tables (e.g., Date, Gender, AgeGroup) to the central fact table (Employee). Setting the direction to single can prevent ambiguous relationships and complex filtering paths that negatively impact query speed. The data model itself is organized as a star schema because the central Employee fact table is directly connected to multiple dimension tables. There are no cascading relationships where one dimension table links to another, which would indicate a snowflake schema.

Microsoft Docs. (n.d.). Create and manage relationships in Power BI Desktop. Retrieved from [Official vendor documentation]. Section "Cross-filter direction."

Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). John Wiley & Sons. ISBN 978-1-118-53026-6. Chapter 3, "Dimensional Modeling Fundamentals," pp. 53-56.

Chen, P. P.-S. (1976). The Entity-Relationship Model—Toward a Unified View of Data. ACM Transactions on Database Systems, 1(1), 9–36. https://doi.org/10.1145/320434.320440. Section 3.4, "Dimensional and Fact Tables."

A clustered column chart is purpose-built for comparing a single quantitative measure (profit) across a limited number of discrete categories (the 10 product categories) in one time slice (the selected quarter). It places uniform-width columns on a common baseline, making magnitude differences easy to judge and category labels easy to read—exactly the goal here.

1. Microsoft Learn – “Bar and column charts in Power BI”

section “When to use a column chart: compare values across a few categories”. https://learn.microsoft.com/power-bi/visuals/power-bi-visualization-types#bar-and-column-charts

2. Microsoft Learn – “Choose the right chart type”

subsection “Compare data across categories – Column charts”. https://learn.microsoft.com/power-bi/create-reports/desktop-choose-right-chart#compare-data-across-categories-column-charts

The requirement is to restrict data access so that users can only view data corresponding to their specific department. This is the primary use case for Row-Level Security (RLS) in Power BI. RLS is implemented by creating roles within the Power BI model. For each role, a DAX filter expression is defined on a table (in this case, the Departments table). When a user assigned to a specific role accesses the report in the Power BI service, this DAX filter is automatically applied, restricting the rows of data they can see throughout the entire model. This method enforces security at the data model level.

B. A parameter is a user-controlled input for interactive analysis, not a security feature. A user could easily change the parameter value to view data from other departments.

C. The CURRENTGROUP DAX function is used within grouping functions like SUMMARIZECOLUMNS to perform calculations on grouped data; it is not related to user-based security.

D. A slicer is a visual filter on the report canvas that users can freely interact with. It does not enforce security, as a user could modify or remove the slicer.

1. Microsoft Learn. (2024). Restrict data access with row-level security (RLS) for Power BI. Microsoft Power BI Documentation. Retrieved from https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls.

Reference Details: The section "Define roles and rules in Power BI Desktop" explicitly states

"You can define roles and rules within Power BI Desktop. When you publish to Power BI

it also publishes the role definitions... To define security roles

follow these steps. 1. Import data into your Power BI Desktop report

or configure a DirectQuery connection. ... 2. From the Modeling tab

select Manage Roles." This directly supports the process described in option A.

2. Microsoft Learn. (2024). Implement row-level security. PL-300: Design and build a data model in Power BI learning path. Retrieved from https://learn.microsoft.com/en-us/training/modules/design-build-tabular-model-power-bi/7-implement-row-level-security.

Reference Details: This official PL-300 courseware notes

"Row-level security (RLS) is a Power BI feature that can be used to restrict data access for given users. Filters restrict data access at the row level

and you can define filters within roles... You will implement RLS by creating one or more roles. A role has a unique name in the model

and it will typically include one or more rules."

3. Microsoft Learn. (2024). Row-level security (RLS) guidance in Power BI Desktop. Power BI Guidance Documentation. Retrieved from https://learn.microsoft.com/en-us/power-bi/guidance/rls-guidance.

Reference Details: The section "Implement RLS" details the process: "In Power BI Desktop

you can create a role by navigating to the Modeling ribbon and selecting Manage roles. In the Manage roles window

you can create

modify

or delete roles... You add a rule to a table by using a DAX expression that must return a TRUE or FALSE value." This confirms that creating roles and defining rules is the correct procedure.

To apply specific background colors based on distinct value ranges, you must use conditional formatting based on rules. The sequence begins by navigating to the column's formatting pane and opening the Background color dialog. Since the colors are based on thresholds rather than color scales or field values, you must then set the Format style to Rules. Finally, to implement the specific coloring for each required range (e.g., 0 to 1, 1 to 2, etc.), you add and configure a new rule for each value range, defining the lower bound, upper bound, and corresponding color.

Microsoft Learn: Apply conditional formatting in tables and matrix visuals. This documentation describes the workflow for applying formatting, differentiating between setting font color/background color and utilizing data bars, color scales, or rule-based formatting.

Source: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-format-tables

Microsoft Learn: Formatting by Rules. Explains that the "Rules" option is used when needing to set a specific color based on discrete value ranges or percentages.

Source: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-format-tables#color-by-rules

The image displays the Power BI service interface. In the top navigation bar, next to the dashboard name "Customers," a tag labeled Sale is visible. This tag represents the shorthand for a data classification that has been applied to the dashboard. Tenant administrators can define these classifications to help users identify the sensitivity level of the data presented.

The main area of the screenshot shows the Power BI Q&A feature, which is being used on the "Customers" dashboard. The Q&A interface explicitly shows the heading INSURANCE above a list of data fields (e.g., insurance policies, customers, account ID). This heading indicates that "INSURANCE" is the name of the dataset the Q&A feature is currently querying to answer user questions for this dashboard.

Microsoft Power BI Documentation, "Data classification in Power BI": This document explains how administrators can set up data classification tags with a name and a shorthand. It states, "Once a classification type is selected, you'll see it displayed next to the dashboard title." This confirms that "Sale" is the shorthand for the data classification.

Microsoft Power BI Documentation, "Use Power BI Q&A to explore your data and create visuals": This source describes the Q&A feature. It clarifies that Q&A operates on the underlying dataset(s) associated with a dashboard or report. The fields and tables listed in the Q&A starting screen are pulled directly from that source dataset, which is named "INSURANCE" in the provided exhibit.

To denormalize the complex snowflake schema (Sales_Region -> Region_Manager -> Manager) into a single table, you should first create a new query. The action Merge [Sales_Region] and [Region_Manager] by using a right join as a new query... initiates this by combining the region and bridge tables; the right join ensures all defined region-manager relationships are preserved. Next, you flatten the hierarchy by performing Merge [Region_Manager] and [Manager] by using an inner join, which pulls in the Manager name details. Finally, to include the Sales_Manager data and consolidate the entire model, you perform Merge [Sales_Region] and [Sales_Manager] by using an inner join, which adds the sales manager attributes restricted to those associated with the sales regions.

Microsoft Learn: Shape and combine data in Power BI Desktop. "You can combine data from multiple sources... Merge queries creates a new query from two existing queries."

Source: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

Microsoft Learn: Merge queries overview. Describes how to merge tables (queries) based on matching columns and join kinds (Inner, Right, etc.) to denormalize data.

Source: https://learn.microsoft.com/en-us/power-query/merge-queries-overview

On-premises data gateway: An on-premises data gateway is only required if the data source is located within a private, on-premises network (e.g., a local SQL Server). For cloud-based data sources like Azure SQL, SharePoint Online, or public web feeds (like OData), Power BI can connect and refresh the data directly over the internet without needing a gateway. Therefore, a gateway is not always needed.

Scheduled refresh: When you publish a dataset to the Power BI service using Import mode, the data is a static copy taken at the time of publishing. To ensure the reports and dashboards reflect the latest information from the source, you must configure a scheduled refresh. This tells the Power BI service to periodically reconnect to the data source and update the dataset.

Basic authentication: Power BI supports multiple authentication methods for connecting to data sources. The available methods depend on what the specific data source supports. Basic authentication, which uses a username and password, is a common method supported by many data sources, including OData feeds, web APIs, and certain databases. Since it is a possible authentication method, the statement "You can use..." is true.

On-premises data gateway:

Microsoft Power BI Documentation, "What is an on-premises data gateway?". This document explicitly states, "The gateway is required if your dataset uses one or more on-premises data sources. The gateway isn't required if all of your dataset's data sources are in the cloud."

Data Refresh:

Microsoft Power BI Documentation, "Data refresh in Power BI", section "What can be refreshed?". This page clarifies that datasets using Import mode need to be refreshed to get updated data. It states, "When you refresh data, Power BI must query for updated data from the underlying data source and then load the updated data into the dataset."

Authentication Methods:

Microsoft Power BI Documentation, "Power Query OData feed connector", section "Connect to an OData feed from Power Query Desktop". This article lists the authentication options for the OData connector, which include Anonymous, Windows, Basic, Web API, and Organizational account. This confirms that Basic authentication is a supported option for at least one common data source type.