Introduction
Imagine having the power to add live comments to your Power BI reports without waiting for model refreshes.
In today’s digital world, data reporting must be efficient, dynamic, and interactive. One powerful way to enhance reporting is by incorporating live commenting functionality directly within Power BI. In this blog post, we will dive into how to implement this feature using Power Apps and SharePoint lists.
* This is a two-part series. In the first part, we’ll focus on understanding the problem, setting the objectives, and laying the groundwork by discussing strategy, personas, and user flows. In the second part, we’ll cover the practical implementation—showcasing live commenting techniques, a detailed demo, and concluding with key insights.
Before we dive in, let me offer a bold statement that perfectly captures the essence of what we’re about to explore. Keep it in mind and reflect on it by the time you finish reading this post
If one does not know to which port one is sailing, no winds is favorable. Seneca
Use Case
We’re building on a real-world use case I encountered a few years back. It’s a common, widely applicable scenario frequently requested in business: enabling write-back functionality from Power BI, most often related to commenting.
To make this showcase a bit more engaging, let’s start with a brief imagination exercise.
Picture yourself as a consultant for a small to medium-sized consultancy specializing in Power BI and the Power Platform.
One day, one of your most important clients sends you an email:
🖋️ Subject: Request for Live Commenting in Power BI
Hi Alex,
I’ve been hearing from the commercial teams that they are spending too much time on processes that require data consolidation and adding comments manually. Is there a way to implement live commenting directly in Power BI to streamline this?
Looking forward to your input.
Best regards,
Jane
As an experienced consultant in both Power BI and the Power Platform, this isn’t the first time you’ve encountered such requests. However, you’re not entirely sure how to respond just yet. The quick, default answer—“it depends” crosses your mind, but you’re a true professional, and this client is far too important for a hasty reply.
So, you take a moment to think it through. Live commenting in Power BI is possible, either through third-party tools or by leveraging Power Apps. However, understanding the client’s existing architecture is crucial. It would be much easier if they had Dataverse or even a SQL database in place, but what if they don’t? Perhaps no complex solution is necessary, and the native commenting features in Power BI may be sufficient. What is exactly the definition of live commenting for the client? There’s simply not enough information to form a clear idea of the best approach. While it’s wise to prepare for the more complex scenarios, sometimes the simplest solution is the most effective.
With this in mind, you reply to Jane:
Dear Jane,
Thank you for reaching out. Live commenting in Power BI is indeed possible through different approaches, though it is not a native feature. The implementation will depend on various parameters, such as the specific requirements, the data sources involved, and how the comments need to be managed.
Could we schedule a meeting to discuss the details further? This will help me better understand the requirements and determine the most suitable solution for your team.
Looking forward to your response.
Best regards,
Alex
Jane replies and connects you with someone from her team : John, a regional sales analyst. He’s the one facing the challenge of consolidating comments, analyzing the data, and delivering insights to executives. You arrange a meeting with John and gather the following information:
As a regional sales analyst, I spend a significant amount of time each month during the closing period extracting sales data for various KPIs from Hyperion into Excel. I then distribute these figures to each country, giving them a week to provide feedback. However, I often receive the comments at the last minute, as the numbers can change, causing delays. This repetitive process of extracting, emailing, receiving, and manually consolidating data leaves me with very little time to thoroughly analyze the numbers. Once everything is consolidated and verified, I need to provide my own commentary and create a PowerPoint presentation for the executive committee (COMEX).
The situation is becoming increasingly stressful as leaders frequently request detailed information about specific countries, and I often feel unprepared. Despite the growing demand for more in-depth analysis and insights, there is no willingness to invest in a new tool. Therefore, the solution must be addressed using Power BI and SharePoint lists to streamline the process and improve efficiency.
You realize that there is a genuine need and significant value to be gained by addressing the issue. You summarize the key points and the impact of the current process in a few words:
– Time Inefficiency
– Work Quality
– Limited Scalability
– Increased Stress
Objectives
Your objectives are as follows:
- Develop a live commenting solution within Power BI (import mode) that incorporates an embedded Power Apps, using SharePoint Lists as the data source. This will enable real-time feedback, reducing delays in report updates.
- Ensure the solution addresses the needs of multiple personas, providing a tailored experience for both country-level users and regional analysts. By doing so, the solution will improve overall workflow efficiency and ensure better data accessibility for decision-makers.
- Success will be measured by the ease of use, level of control over the commenting process, and its ability to reduce time spent on manual data consolidation.
Sneak peak of the final solution
Here’s a glimpse of the final solution I built for this use case. If you’re interested in exploring the full journey—the thought process, decisions, and strategies that shaped this solution— keep reading to dive deeper into how it all came together.
Where to Start
Sketching, Personas & User Flow
In today’s increasingly complex world, building a Power BI report isn’t just about creating visualizations.
In this example, we need to integrate a Power Apps inside a Power Bi and we need to think about how these tools should interact with one another. What features should be developed in each of the tools? To build successful solutions, we must go beyond traditional best practices and incorporate Design Thinking into our process.
While we know the importance of sketching, defining personas, and mapping user flows when developing Power BI reports, this approach is even more critical when combining multiple tools into one unified solution. Honestly, without these steps, achieving a truly functional and user-friendly result is highly unlikely.
So, how do we start?
By sketching the solution based on the needs of each persona. In this particular case, we’re developing a solution that allows different countries to provide input and comments. The regional analyst can then review, add comments of his/her own, and manage when the process should be open or closed.
PERSONA 1: Countries
Let’s zoom in on one persona: Mario from Italy. Mario represents a user working at the country level, responsible for inputting comments on specific KPIs (in this example, we’ve defined four). For Mario, the solution needs to be intuitive and efficient.
Imagine Mario, sitting at his desk, wanting to add his comments for the latest KPI updates. He expects to easily select the desired industry using a slicer. Once he chooses, the interface should clearly show him where comments are required. If he’s already provided input, he wants to be able to review what he wrote earlier. He’s looking for a simple, user-friendly experience.
Now, Mario’s not just interested in adding plain text. He wants control over formatting. He needs to highlight certain information in bold, change font size and colors to emphasize key points. A clear “Save” button is essential—he needs the assurance that his work is saved and secure with just one click.
But it doesn’t stop there. Mario is also concerned about data privacy. He wants full confidence that only the regional analyst can view the sensitive data and comments he’s entering. Ensuring the right level of access and security is paramount.
Now that we’ve stepped into Mario’s shoes and understand his needs, it’s time to bring these ideas to life and sketching them out on paper. This initial sketch will give us a high-level view of what the solution should look like, allowing us to visualize the key elements and interactions before diving into development.
PERSONA 2: Region
Now, let’s focus on our second persona: the regional analyst, Julie. Julie’s responsibility is to consolidate KPIs across all industries and countries. But does she need to see the information differently than Mario? Let’s revisit the sketch we built for Mario, our first persona, and think it through.
Mario was focused on selecting an industry, reviewing KPIs, and adding comments specific to his country. If Julie were to look at the same screen, she would need an additional filter to select the country she’s interested in. However, this setup doesn’t offer much value to her. Julie’s role revolves around analyzing data at the country level—benchmarking countries, comparing results, spotting variations, and reviewing comments. This is how she typically consolidates the data.
Fortunately, you anticipated these needs. After your workshop with John, you asked him to share the reports he currently uses, how he presents the data, and how it’s shown to executives. Understanding how your personas interact with data is critical. It’s clear that the screen designed for Mario won’t serve Julie effectively.
So, you adapt the design. Instead of displaying KPIs filtered by industry, you reorganize the view to show countries, with filters for both industries and KPIs. You also know that Julie will need to add regional comments, but she shouldn’t be able to modify the comments made by individual countries. To address this, the data should be presented in “read mode,” rather than “edit mode,” for country-level input.
Additionally, one of Julie’s key tasks is to manage the process, specifically closing the commenting phase once the data is consolidated. She needs a simple, intuitive way to open or close the process, locking the ability for countries to make further changes. This final action must be easily accessible and straightforward, ensuring the process is smooth for her and aligned with her workflow.
The concepts outline above are integrated into the sketch
The Importance of User Flow
By taking the time to sketch out user needs, define personas, and map the user flow, we are laying the groundwork for a well-thought-out and functional solution. This process goes beyond simply meeting technical requirements; it’s about making the tool intuitive, seamless, and genuinely valuable for the end user. When we carefully consider the journey of each persona, we can foresee the potential challenges they may encounter and proactively design solutions to ensure a smoother, more effective experience.
The user flow is the key element here. It’s the roadmap that guides how different personas, like Mario or Julie, will interact with the system, ensuring that their individual needs are met in a logical, user-friendly way. For instance, Mario’s journey starts with selecting an industry, viewing KPIs, and adding comments, all within a simple and secure interface. Julie’s user flow, on the other hand, involves comparing data across countries and consolidating results, which requires a different structure that allows her to benchmark and add regional comments.
At the country level, the core expectation is the ability to add comments for specific KPIs. In our example, we defined four main KPIs that need attention. The user flow for Mario, or any country-level user, is designed to ensure ease of access to these KPIs, making it straightforward to select an industry, view the relevant metrics, and add or update their insights. The flow anticipates their need for clarity, simplicity, and data privacy at each step of the process.
Ultimately, this approach involving sketching the solution, understanding personas, and defining a clear user flow, transforms the tool into more than just a functional system. It becomes a well-crafted experience, tailored to the specific roles and responsibilities of each user, ensuring they can achieve their goals efficiently and with confidence.
Sharepoint List Structure
We are well on the path to success, but now it’s time to define how our data source structure will look. We need to set up our SharePoint lists to store the information in a way that aligns with the solution we’ve sketched.
How many SharePoint lists do we really need to support our requirements? Three? One for country-level data, one for region-level data, and another for opening/closing the process? Actually, if we think about it, the answer is no.
There’s no need to complicate things unnecessarily. If we aim to build a scalable solution, simplicity is key. Why create separate SharePoint lists for country-level and region-level data if they essentially store the same type of information?
Let’s start by considering the needs of the country persona. We need a Title field for the name of the solution, a Country field to capture where the comment is coming from, the KPI and Industry, and of course, the Comment itself. Additional fields like the Created by and the last Modified Date will also be useful for tracking. Fortunately, these columns are automatically generated.
Now, looking at the region persona, the data structure is nearly identical. The only difference is that instead of a country, we’ll have an entry labeled “Region.” This can easily be accommodated in the existing Country column. Countries won’t see regional data, thanks to row-level security we’ll implement in Power BI, ensuring each user views only the data relevant to them.
So, rather than having separate lists, one SharePoint list can effectively serve both personas. The second SharePoint list will handle whether the process is open or closed, which only requires two columns. It’s simple and effective with no unnecessary complexity.
By simplifying our SharePoint list structure, we reduce complexity, making it easier to maintain and scale the solution. A single list for both Country & Region analysts not only streamlines data management but also ensures that future use cases can be easily integrated without overhauling the architecture.
Conceptual Modelling & Run complexity anticipation
Before diving into development, it’s crucial to think conceptually about the model and how the solution will function in terms of RUN complexity. For this specific use case, we need to focus on ensuring we have the right strategy and plan in place to meet the desired outcomes.
*Run complexity refers to how efficiently a solution operates, especially when dealing with dynamic processes like live commenting involving different solutions like Power Bi and Power Apps.
We already know that our data source for comments will be based on SharePoint lists. We also know that the model will be in import mode for now, but the architecture needs to be flexible enough to work in various scenarios, even with direct query data. The architecture should be solid and scalable, allowing us to quickly adapt to other use cases in the future.
Let’s break down the challenges we need to address:
– The main difficulty is implementing live commenting within a model that isn’t “live.” As a reminder, the model will be in import mode.
To avoid any confusion, let me clarify what I mean by “live commenting” and what we will implement in this specific use case:
“Live commenting in Power BI means that users can add comments and see them applied instantly, without having to wait for the scheduled model refresh.”
Now that we’ve clarified that, let’s explore how we can achieve this conceptually:
1. To enable real-time commenting, we should avoid integrating the SharePoint data source directly into the Power BI model.
This is essential because if the data is part of an import semantic model, live commenting won’t be possible.
2. Comments should be displayed in a separate Power Apps visual.
If we want live commenting in Power BI, the comments must be added and displayed in Power Apps without directly connecting to Power BI. Despite being separate tools, the integration needs to feel seamless, with consistent design and interaction between Power BI and Power Apps.
3. If the Power BI model isn’t affected, how will Power Apps respond to slicer changes in Power BI?
This is the final challenge. To implement live commenting without affecting the data model, we need to bridge the gap between Power BI and Power Apps. The solution lies in using PowerBiIntegration within Power BI. We’ll explore this further during the demo.
User Story, Development & Testing Strategy
Let’s take a moment to recap the steps we’ve covered so far:
1. We began by understanding the business need, identifying the problem, and analyzing the existing process.
2. We adopted the perspective of our client, developed personas, and sketched out solutions tailored to the unique needs of each persona.
3. We identified the interdependencies between these personas, conceptualized the solution, and set up the data sources to align with our plan.
Now, we’re just one step away. We need to consider our solution from a maintenance and complexity standpoint. Integrating different tools naturally introduces complexity, so it’s critical to minimize that complexity and think ahead to troubleshooting. While we could create dependencies across various layers between Power BI and Power Apps, our goal is to avoid building multiple bridges, as that would increase troubleshooting time. Instead, what we want is a single, unified “bridge” between the tools—one that’s easy to test and maintain. That bridge is PowerBiIntegration.
With the strategy, personas, and overall solution framework now in place, it’s time to shift from planning to execution. In the next post, we’ll dive into the practical implementation, where I’ll walk you through the key techniques, set up live commenting, and demonstrate how everything comes together to bring the solution to life.
Get ready for the hands-on part of the journey, coming next week!!
Thanks for your personal marvelous posting!
I quite enjoyed reading it, you are a great author.I will remember to bookmark your blog and will
often come back sometime soon. I want to encourage that you continue
your great writing, have a nice weekend!