Recent Posts
- A Live Commenting Use Case in Power BI: A Deep Dive into Techniques and Implementation (part 2)
- A Live Commenting Use Case in Power BI: Laying the Groundwork for Success (part 1)
- Enhancing Power BI’s Decomposition Tree with DAX
- A new chapter for interactive navigation in Power Bi
- An Alternative to Spaghetti Chart
Introduction
Imagine having the power to add live comments to your Power BI reports without waiting for model refreshes.
In today’s digital landscape, data reporting must be efficient, dynamic, and interactive. A powerful way to enhance this experience is by incorporating live commenting functionality directly within Power BI. In this blog series, we explore how to bring this feature to life using Power Apps and SharePoint lists.
⚓ Seneca’s Wisdom: “If one does not know to which port one is sailing, no wind is favorable.”
The Two-Part Series: A Quick Recap
– In Part 1, we explored the problem, defined our objectives, and laid the strategic groundwork.
– In Part 2 (this post), we’ll dive into the practical implementation, showcasing live commenting techniques and key takeaways.
If you rather prefer following a video presentation on this topic, click on the link below:
Demo and deep dive on main techniques
In this section, we’ll dive into the practical techniques needed to implement live commenting in Power Bi.
We’ll explore:
1. DAX as a parameter
2. Making the screen wider
3. Adding new fields
4. Main development techniques for each persona
1. DAX as parameter
Start from a matrix in PBI
Let’s kick things off with one of the most fundamental best practices: validating your data before building charts or integrating Power Apps.
Before diving into charts or advanced visuals, always validate your figures and interactions using a basic matrix in Power BI. This step helps ensure that your calculations behave as expected, making the integration process smoother.
Let’s start by creating the matrix in Power Bi as follows:
A key DAX measure we’ll use throughout this solution:
_01 Level Test =
IF (
COUNTROWS (
VALUES ( Customers[Country] ) ) = 1,
"BU", "BLR"
)
This measure checks the number of distinct countries selected:
– If the count is 1, it returns “BU” (Business Unit), indicating a single-country view (Country persona).
– If the count is greater than 1, it returns “BLR” (Region view), indicating a multi-country view (Region persona).
This distinction is crucial for determining the user context and adapting the embedded Power Apps experience accordingly.
💡Pro Tip
Anticipation is key when working with Power BI and Power Apps integration. Make sure your DAX measures align with the intended business logic and user experience.
_02 Selected Industry =
SELECTEDVALUE( Industry[Name])
This measure captures the selected industry from the slicer on the page.
_03 Selected Country =
SELECTEDVALUE( Customers[Country])
This measure captures the selected country from the slicer on the page.
DAX vulnerabilities: Consider Business Logic
When designing DAX measures, it’s essential to ensure they align with the business logic. In our example, the 01 Level Test measure assumes that the Country persona has access to only one country. If a user is responsible for entering comments for multiple countries, this measure will fail. Always validate your DAX logic against the expected business scenarios and document any assumptions clearly.
Additionally, consider the visual implications during development. For instance, if we overlook the possibility of users selecting multiple countries or industries with the slicer, the DAX measure could easily throw errors or display incorrect results. Anticipate these scenarios to avoid potential pitfalls and enhance the overall robustness of your solution.
For this reason, taking the time to carefully consider potential weaknesses in your DAX logic and user interactions is a valuable step in the development process. It helps you anticipate issues early on, preventing problems down the road.
Integrating with Power Apps: Best Practices
Step 1: Publish Your Report to the Power BI Service
Before creating or modifying Power Apps, publish your Power BI report to the Power BI service. This is a recommended practice by Microsoft to ensure seamless integration. For more details, refer to the official documentation:
Step 2: Test Power BI Integration Inside Power Apps
Once your report is published, test the integration between Power BI and Power Apps. Ensure that your DAX measures are functioning correctly and that the selected values are being passed accurately.
Step 3: Rename, Save, and Publish the Power App
After testing, make sure to rename the Power App appropriately, save your changes, and then publish the app. This step ensures that your integrated solution is visible inside the Power Bi report.
2. Make screen wider
Once you publish your Power App, it should appear embedded in your Power BI report.
However, you might notice an issue: the app initially displays in a small, phone-like layout, which limits visibility and usability. Our goal is to make the embedded app wider, utilizing the full width of the screen for a clearer and more accessible user experience.
Change Display Orientation
To address this issue, follow these steps:
1. Open Power Apps settings.
2. Change the Display Orientation to “Landscape” mode.
3. Disable the “Scale to Fit” option.
These adjustments will resize the app layout to better fit the Power BI report canvas.
After making these changes, save your app and publish it again. This step ensures the new layout is applied when the app is displayed within Power BI.
⚠️ Important Note: Refresh Delay in Power BI
Be aware that changes made in Power Apps are not immediately visible in Power BI. This can lead to confusion during development if you don’t realize the app is still showing a previous version. To confirm that the latest version is displayed, I recommend adding a temporary visual indicator, like a red rectangle, and moving it slightly with each update. This way, you can easily check if the changes have been applied.
💡Pro Tip
To force the refresh and speed up the process of seeing updates in Power BI, try switching between different report tabs (pages). This can help trigger a quicker reload of the embedded app.
3. Add new fields
Once you’ve set up the integration between Power BI and Power Apps, making changes, like adding new fields, can be quite challenging. Most users resort to deleting the entire integration and starting over, which is a drastic and time-consuming approach. I was once in that group, and after learning this the hard way, I now spend much more time upfront, carefully planning to anticipate future development needs and avoid rework.
However, there is a method to add new fields without starting from scratch. It’s not the most intuitive or reliable solution, but it’s important to be aware of it as a potential workaround.
How to Add New Fields Without Rebuilding the Integration
1. Use Power BI on the Service:
– This workaround only functions if you are working within the Power BI Service, not in the desktop environment.
2. Edit the Power Apps Visual:
– Click on the three-dot menu (more options) on the Power Apps visual in Power BI and select “Edit“.
– This action will open a new browser tab with your Power App in edit mode, updated to include any new DAX measures or fields you’ve added to your Power BI model.
⚠️Warning
This method isn’t always reliable. Sometimes the changes won’t reflect properly, and you may still need to recreate the integration if issues persist. The best practice is to anticipate all required fields and measures before starting the integration to minimize disruption.
4. Main development techniques
Country persona
For each KPI displayed on the report, there is a dedicated form in Power Apps. In this case, I have 4 distinct forms, each linked to a specific KPI. These forms are configured independently. The goal is to synchronize user interactions in Power BI with the embedded Power Apps application, allowing the screen to adapt dynamically based on user selections.
This seamless interaction is achieved through several techniques, including managing the default mode of the form. Here’s what that involves:
Managing the Default Mode of the Form
When users add comments via Power Apps embedded in Power BI, we need to determine whether we should create a new entry in the SharePoint list or edit an existing line. This scenario is a new challenge compared to a typical Power BI-only setup, where data is read-only. Here, we are writing back to the data source, and that requires a specific approach to manage insert vs. update operations.
The logic is implemented using the following formula:
If(
First(
Filter(
CommentsBU_Demo,
Title = "Demo Comment" && KPI = "Revenue Lost" && Industry = First(PowerBIIntegration.Data).'_02 Selected Industry' && Country = First(PowerBIIntegration.Data).'_03 Selected Country'
)
).KPI = Blank(),
FormMode.New,
FormMode.Edit
)
This formula checks if a matching comment already exists in the `CommentsBU_Demo` table for the selected Industry, Country, KPI, and Title:
– If no matching record is found (i.e., the `KPI` field is blank), the form is set to New Mode, allowing the user to add a new comment.
– If a matching record exists, the form is set to Edit Mode, enabling the user to update the existing comment.
Why This Matters in Embedded Scenarios
– Dynamic Form Adaptation: The form automatically switches between New and Edit modes based on the user’s context in Power BI.
– Data Consistency: Ensures that the user modifies the correct entry or adds a new line as needed, preventing duplicate or incorrect data submissions.
Handling Hardcoded and Dynamic Data
In the formula, some parts are hardcoded, such as Title = “Demo Comment” and KPI = “Revenue Lost”. This is intentional, as I have created 4 separate forms, each corresponding to a specific KPI. This setup sets the grounds for implementing a pixel-perfect alignment with the KPI card visuals in Power BI, maintaining a consistent and integrated look.
– Each form has a unique hardcoded KPI value, and when a comment is added or edited, this KPI information is stored in the SharePoint list.
– Standard fields like “Created By” and “Modified” are automatically populated by SharePoint, simplifying data entry for the user.
Automating Data Entry for User Context
The main focus of this integration is the comment section, where users provide feedback or notes. However, I do not want users to manually enter context data like KPI, Country, or Industry, as this should be derived from their current selections in Power BI.
– Dynamic Data Capture: Power BI slicer selections are passed to Power Apps using `PowerBIIntegration.Data`. This allows us to automatically populate context fields (Industry, Country) in the SharePoint list, streamlining the process for users.
– Predefined Values for Regional Persona: If the user is a regional manager, the Country field is set to “Region” by default. This avoids the need for a separate data source and keeps the solution simple, facilitating easier maintenance and debugging.
Repeating the Logic Across Forms
This logic is applied consistently across all 4 forms, one for each KPI. By replicating the structure and methodology, we maintain uniform behavior, making the solution scalable and easier to manage.
Item Property
The Item property in this setup controls which record the form displays by default based on the user’s filter selections in Power BI.
First(
Filter(
CommentsBU_Demo,
Title = "Demo Comment" && KPI = "Revenue Lost" &&
Industry = First(PowerBIIntegration.Data).'_02 Selected Industry' &&
Country = First(PowerBIIntegration.Data).'_03 Selected Country'
)
)
This function retrieves the first relevant record from the CommentsBU_Demo table, filtered by the current Power BI report selections:
– Title is fixed as “Demo Comment“.
– KPI is set to “Revenue Lost“.
– Industry and Country dynamically reflect the current selections from the Power BI filters.
Why This Matters
This function enables the form to display the appropriate comment data based on Power BI filters, creating a smooth, context-driven experience. For example, if the user selects “Retail” as the industry and “France” as the country in Power BI, the form will automatically show the relevant comment for “Revenue Lost” within that context.
💡Pro Tip
To focus only on the comments, other columns in the forms are “hidden” by setting their X & Y coordinates to 0 and Visibility to false. This ensures users see only the most relevant data.
In the Item property, this formula provides:
– Dynamic Data Context: The form adapts based on the Power BI selection.
– Automatic Record Retrieval: The first matching record is displayed automatically, minimizing user effort in finding the correct entry.
– Seamless Integration: Connecting Power BI filters to Power Apps forms enhances the experience by providing real-time, relevant data.
💡Pro Tip
For comments, the default TextInput is replaced by a Rich Text Editor, allowing users to format their comments with options like bolding, highlighting, color changes, and size adjustments. This flexibility replicates the familiar functionality from Excel or PowerPoint, encouraging users to communicate critical information effectively.
Visibility Logic
Visibility is another core feature in this setup. For instance:
First( PowerBIIntegration.Data ).'_01 Level Test' = "BU"
This formula dynamically adjusts visibility based on the Power BI user’s role:
– Country Persona: Displays the four forms aligned with the Power BI KPI card visuals.
– Region Persona: Shows a different interface with distinct logic and interaction.
Essentially, this works like a conditional bookmark in Power BI but controlled by measures, allowing a tailored interface for each persona. Unlike Power BI, in Power Apps, when items are hidden, they effectively “don’t exist” on the screen, preventing them from impacting the user’s view.
💡Pro Tip: Rename & Group elements
Consistently rename and group elements across Power BI and Power Apps for easier navigation and maintenance. Your future self will appreciate!
SAVE button
A crucial feature for the Country persona is the Save button:
– The Save button activates only when a comment is modified, providing a visual cue to the user to save changes.
– Once clicked, the button disables, confirming that the comment was saved successfully. Additionally, the updated comment displays instantly, reassuring the user that the data has been saved.
💡Pro Tip: User Experience
While this save confirmation behavior feels natural, it needs to be explicitly created in Power Apps. It’s not a built-in feature. Implementing it requires custom logic and attention to detail
Summary of Main Features for the Country Persona
To recap, we’ve covered:
– Forms’ Role in Live Comments: Forms can enable dynamic commenting directly within Power BI.
– Power BI Integration: The logic embedded in Power Apps leverages Power BI slicers to ensure seamless data sync.
– Enhanced User Experience: Rich text formatting, contextual visibility, and save confirmations create a familiar, user-friendly experience.
This cohesive integration enhances Power BI’s interactivity, creating a powerful, streamlined live commenting feature that adapts naturally to each user’s context.
REGION persona
Different persona, different needs, and different features to implement.
Unlike Country personas, who require interactive forms, the Region persona’s needs are met with a simpler, read-only setup.
Here’s how it works:
Since the Region persona operates later in the workflow, all features relevant to this persona are grouped and controlled through a DAX measure from Power BI, enabling visibility and access only to designated features. This approach eliminates any editable forms, as the Region persona should not modify comments submitted by Country personas. Instead, their view is a read-only Gallery displaying the comments, ensuring data integrity.
Core Features for the Region Persona
1. Ability to Add an Overall Comment:
– The Region persona has the option to add a general comment summarizing or addressing the broader regional perspective, without affecting individual country-level comments.
2. Ability to Open/Close the Process:
– The Region persona controls the status of the commenting process, marking it as open or closed based on requirements. This is a simple, high-level control allowing them to finalize or reopen comments as necessary.
Let’s examine the two primary techniques in more detail.
The Pop-Up menu
A Pop-Up Menu is used to facilitate both features, providing an intuitive interface for the Region persona to manage their specific tasks:
– Adding an Overall Comment: The pop-up allows the Region persona to input a general comment without modifying any specific country’s data. This comment can be formatted using a Rich Text Editor to maintain a high level of readability and emphasis, as needed.
– Opening/Closing the Process: The pop-up includes a toggle option that the Region persona can use to set the process status. A simple switch or button enables them to mark the process as “open” for additional comments or “closed” to finalize the input stage.
This approach keeps the interface streamlined and intuitive for the Region persona, who has a more administrative role and requires minimal interaction with the data itself.
Pop-up menu closed
Pop-up menu open
CommentsStatus Data source
Summary of Region Persona Features
For the Region persona, the user experience is designed to be straightforward, focusing on:
– Read-Only Comment Gallery: Display comments without editable forms.
– Pop-Up Menu for Summary Comments: Easily add regional summaries without impacting country data.
– Process Control Toggle: Enable open/close control to manage the commenting lifecycle.
By tailoring features to this persona’s role, we ensure a simplified yet effective interface, aligning with their end-of-process responsibilities while preserving the integrity of country-level comments.
Conclusion
This two-part series on Live Commenting in Power BI has walked through the entire process of building a flexible, user-friendly commenting solution embedded within Power BI, leveraging Power Apps. Through the journey, we covered both the Country persona and Region persona perspectives, each with distinct requirements and tailored features. Here’s a final wrap-up of the insights from this project:
1. Transforming Power BI into an Interactive Tool: Traditionally a data visualization platform, Power BI can be enhanced with interactive capabilities (beyond the native ones) using Power Apps, transforming it into a powerful tool for live data capture and feedback. The live commenting feature pushes the boundaries of Power BI, allowing users not only to view insights but also to contribute to them in real time.
2. Adaptability Across Personas: Each persona required a unique approach. The Country persona focused on interactive forms for real-time comment entry and edits, allowing them to contribute granular insights. Conversely, the Region persona needed a streamlined, read-only view with the option to add high-level feedback, demonstrating the versatility of embedded Power Apps to cater to different roles.
3. Power BI Integration Techniques: We explored various integration techniques—using Power BI slicers to pass contextual information to Power Apps, dynamically adjusting form modes, and controlling visibility based on role-specific measures. These techniques ensure the app adapts seamlessly to the context and remains intuitive for users.
4. Enhanced User Experience with Custom Controls: Elements like the Save button with change detection and Rich Text Editor significantly enhance usability, allowing users to format comments and receive immediate feedback on saved changes. These features add polish to the solution, making it feel like a cohesive extension of Power BI.
5. Process Control and Data Integrity: The ability to open and close the commenting process for Region personas ensures that data entry is controlled and finalized in stages. This not only supports efficient workflow management but also maintains data integrity, ensuring that comments reflect the state of analyzed feedback.
6. Scalability and Maintenance: Implementing consistent naming conventions, grouping elements, using hidden controls to manage visibility, using a limited number of data sources & screens developed, set up a solution that is scalable and maintainable. These practices are crucial as the solution can evolve.
Final Takeaways
– Empower Your Audience: Embedded Power Apps bring Power BI to life, allowing users at every level of the organization to actively participate in data narratives. For the right scenarios, this approach can democratize feedback and improve organizational alignment.
– Future Possibilities: This live commenting setup lays the groundwork for further innovations, like integrating more complex workflows, adding notifications, or enabling advanced filtering based on user roles.
With the completion of this series, you now have a foundational framework for building live commenting solutions within Power BI. This integration shows that with careful planning and the right tools, we can enhance the interactive experiences that drive engagement and insight.
Expand Ideas
This solution could have been enhanced in various ways. For instance, we could have embedded a Power Automate flow to send reminders directly from Power BI or set up automated weekly/daily update emails. We could also have integrated the Power BI report into an app and embedded it in Teams or PowerPoint.
In short, the possibilities are endless. It’s amusing to think: a Power App embedded in a Power BI report, embedded in an app, embedded in PowerPoint, embedded in Teams.
But where do you draw the line? That’s for you to decide. One critical reminder: Documentation!
And remember, just because you can build complex solutions doesn’t mean you should. The real challenge lies in creating simple, scalable solutions—that’s the true mark of expertise.
Recap Best Practices
