The app is where work happens. The model is where performance becomes visible.
A performance scorecard app can collect goals, results, comments and approval decisions in one place. The next request is usually to show whether teams are on track, which goals are waiting for approval and how results have changed over time. Dataverse is a natural source for that report, but loading every app table into Power BI and placing fields on charts is not a data model.
The useful design starts by identifying the grain of each table, the relationships between them and the rules behind each KPI. Power Automate then supports the process around the report: it can coordinate a refresh, alert an owner when a result crosses a threshold, and deliver a snapshot when somebody needs a file. It should not become a substitute for a well-designed semantic model.
Start with the business grain
Imagine a scorecard app with a Goal table, a Scorecard Result table and an Approval table. A goal defines the target and its owner or team. Each result records an observed value for a goal and reporting period. Approval records capture the decision, approver and decision date. Keep those events separate: one result can have more than one approval event, and a goal can have many results over time.
In Power BI, a practical star schema could use DimDate, DimTeam, DimEmployee and DimGoal dimensions around FactResult and FactApproval. Use the Dataverse GUID keys for relationships, and bring readable names from lookup columns for report labels. Avoid joining fact tables directly; dimensions should filter facts through clear one-to-many relationships. If targets change over time, store target history at the correct effective date or period instead of silently applying today’s target to old results.
The Dataverse connector supports Import and DirectQuery. Import is often the better starting point for a responsive analytical report; DirectQuery can be useful when freshness and Dataverse’s user security context matter more than query speed. Validate the actual environment, table volume and security behavior before choosing. The connector is designed for analytics, not bulk extraction.
Connect and shape the Dataverse tables
In Power BI Desktop, choose Get data > Power Platform > Dataverse, select the environment and load only the tables and columns the model needs. Expand lookup columns deliberately. A lookup commonly exposes both an ID and a display name; relate on the stable ID and use the name for presentation.
Choice columns also need care. The connector exposes a stored value and a friendly label for single-select choices. Use the label in visuals when readers need to understand a status, but keep the numeric value available for stable logic. Filtering repeatedly on label fields can add work to queries. Multi-select choices are less straightforward: the connector may return comma-separated integer values without corresponding labels. If those selections are central to reporting, normalize them into a related table or a curated dataflow rather than building fragile text parsing into every measure.
A small Power Query cleanup makes the model easier to maintain:
let
Source = CommonDataService.Database("contoso.crm.dynamics.com"),
Results = Source{[Schema="dbo", Item="new_scorecardresult"]}[Data],
KeepColumns = Table.SelectColumns(
Results,
{"new_scorecardresultid", "new_goalid", "new_periodstart", "new_actual", "new_status"}
),
Typed = Table.TransformColumnTypes(
KeepColumns,
{{"new_periodstart", type date}, {"new_actual", type number}}
),
Submitted = Table.SelectRows(Typed, each [new_status] = 100000001)
in
Submitted
Replace the sample logical names and status value with the schema from your environment. Keep transformations foldable where possible, and check the query plan and refresh time with realistic volumes. A friendly status label is useful for display; a stable option value is usually safer for filtering logic.
Define target attainment explicitly
The measure needs a clear denominator and a clear rule for aggregating results. For a simple model where each result row has an actual value and its applicable target, these measures are a starting point:
Actual Result =
SUM ( FactResult[ActualValue] )
Target Result =
SUM ( FactResult[TargetValue] )
Target Attainment % =
DIVIDE ( [Actual Result], [Target Result] )
Approved Results =
CALCULATE (
COUNTROWS ( FactResult ),
FactResult[ApprovalStatusValue] = 100000002
)
Pending Approvals =
CALCULATE (
COUNTROWS ( FactApproval ),
DimApprovalStatus[Status] = "Pending"
)
Those sums only make sense if actuals and targets share compatible units and grain. If a goal is measured as an average, percentage, completion state or weighted score, define that calculation separately. Do not average percentages across teams when the business question calls for a weighted total. Use a shared date table, mark it as the date table, and decide whether visuals use result date, period start or approval date.
Put Power Automate around the reporting model
Power Automate is useful when an event or business action should follow a model refresh or a metric result. A flow can run after a Dataverse approval changes, wait for a batch to settle, and request a Power BI semantic model refresh. For busy apps, avoid one refresh per edited row: queue or batch changes, then refresh at a deliberate interval. A refresh request is asynchronous, so a flow should check completion before claiming that a report is current or sending a snapshot.
For alerts, use a scheduled flow to query a carefully defined KPI or a model-driven alert pattern, then notify the team owner when attainment falls below an agreed threshold. Include the date range, team and metric definition in the message. An alert that says only “score is low” creates noise; an alert with context can lead to an action.
For delivery, Power Automate can distribute a link or export a report to PDF or another supported format, subject to the workspace, licensing and export constraints in the tenant. Prefer a secure report link when readers need to interact with filters. Export a snapshot when the recipient needs a point-in-time record, such as a monthly approval packet. Respect the report’s access rules before sending files by email or saving them to a broadly visible location.
Do not add a flow just to schedule a normal refresh. Power BI’s built-in scheduled refresh is simpler when a predictable cadence is enough. Consider a dataflow or Fabric pipeline when shaping and reusing data across multiple models, handling larger ingestion workloads, or separating operational app tables from analytical storage. Power Automate fits event orchestration and notifications; it is rarely the right engine for repeatedly copying a large Dataverse history row by row.
Security, volume and refresh pitfalls
Dataverse security roles and Power BI row-level security are related but not interchangeable. With DirectQuery, the Dataverse security context can be relevant to what a report user sees, depending on the connector and service configuration. With Import, the model contains a refreshed copy, so design Power BI permissions and RLS deliberately. Replicating complex Dataverse access rules in RLS can require ongoing synchronization. Test using real viewer accounts, not only the report author’s credentials.
Power Apps delegation warnings concern whether an app query can be processed by Dataverse or only against a limited local subset. That is separate from Power BI’s connector and refresh behavior, although both can expose the same data-volume problem. Keep app formulas delegable where possible, and validate Power BI row counts independently so that a scorecard does not quietly report an incomplete population.
Incremental refresh can reduce repeated work for a large, date-partitioned history, but it is not automatic just because the source has a date column. Configure RangeStart and RangeEnd, verify that filters fold to Dataverse, and test the published refresh policy. If a record can be edited long after its reporting period, choose a refresh window or change-tracking strategy that can capture those late updates.
A short implementation checklist
- Confirm the grain of goals, results and approval events; document how targets change over time.
- Connect with the Dataverse analytics connector and load only required tables and columns.
- Relate tables with GUID keys, use a proper date dimension, and avoid fact-to-fact joins.
- Check choice labels, multi-select fields, lookup expansion and query performance.
- Define attainment, weighting, approval status and date semantics with the business owner.
- Decide whether Import or DirectQuery meets freshness, performance and security needs.
- Use built-in refresh for a fixed cadence; use Power Automate for event coordination, alerts and controlled delivery.
- Test RLS and Dataverse access with representative users, including late edits and larger volumes.
A scorecard dashboard earns trust when every number can be traced to a defined result, target and period. Dataverse provides the operational records, Power BI provides the analytical model, and Power Automate connects the report to the next business action.