Where does the Power BI Mobile app fit within your report building plan? This topic is widely un-discussed within the Power BI community. Many blogs and tutorials focus mainly on developing Power BI reports for a desktop screen. More specifically, a report design that fits a wide screen monitor. However, building reports for the mobile app require additional considerations.
The Current State of Mobile
First before we get to the recommendations for mobile reporting developments, let us discuss the current status of building a mobile report. To create a mobile centric view Power BI desktop follow these steps:
Click on the View ribbon
Click on the button labeled Mobile layout
Click on the Mobile Layout
Portrait Mode Only
This takes you to a new screen where the mobile device is positioned in portrait mode. At the time of writing this article there are no options to build a mobile view in landscape. If you desire to have a mobile report that can be viewed in landscape mode you actually have to create a normal report view but with a narrower screen.
View of building reports in the mobile view.
Simplified Build Experience
The report canvas is greatly simplified. Meaning you don’t get all the advanced features of a normal report page. Stacking visuals on top of each other is not an option. Changing the color of the background is not an option on this screen. Instead, to change the mobile page background color you have to leave the mobile view. Return to desktop mode change the color and then return to the mobile view.
An Alternative Approach
Due to the limitations mentioned above. We propose that you build a report page with custom dimensions. From my experimentation a width of a page from 600 to 800 pixels meets my needs. The page length can be as long as you need it so that all the required visuals can fit on the page. Previously pages have grown to 1000 to 2000 pixels in length.
How to customize the page size?
Follow these instructions to change the page size
Click somewhere on the Report page to deselect any visuals
Open up the Visualizations Pane
Click on the Format button; the one that looks like a paint roller
Open the section named Page Size
Change the drop down menu for Type to be Custom
Type in a new Width and Height for the page
Change View of Report for scrolling
By making the page narrower but longer, the default view of the report will cause UX challenges. To fix this the report is will need to render as a scrollable object. To do this we adjust the view settings of the page
Click on the View ribbon
Click on the button named Page View
A drop down menu will appear
From this dropdown menu Click on the option named Fit to width
Fit Page to Width
Other UI / UX Considerations
As a report designer it is important to consider the UI for report consumers. In most reports I design everything that the user can see fits on a single page. Scrolling on a page is not a major issue. As we introduce scrolling on mobile we run into issues with some visuals.
Table Visuals Cause Issues
Take for example a table visual. This visual it’s self has scrolling built in. Thus, if you are scrolling a page on mobile when you touch inside the table and swipe up the mobile experience swipes the table visual. This UX can lead users to get suck inside a table when attempting to scroll the page.
Table Scrolling Solution
A solution to address users getting stuck inside a table is to provide a pixel boarder. The boarder can be either dual boarders on the left and right or just one boarder on the left or right. Inside this boarder do not place any visuals that would require scrolling, such as a table.
Adding a Scrim for added Clarity
In addition to just retaining a pixel gap on the sides I recommend also adding some color to the background. The concept of the color either in blocks or via a gradient color. See the following sample scrim for reference.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
On the latest Explicit Measures Podcast (Episode 5), the team dived into what should a BI Team focus on for their own KPI’s. One theme was consistent across each host however: Any KPI for a BI Team starts with the question: how do you evaluate and define success? This idea of success and the value for a Power BI pro can fall into many different opinions, depending on the size, team, and current culture at an organization. We wanted to share an initial template of KPI’s that any BI Team or Pro should start using and integrating in their own workflow.
Evaluating Success for Power BI
How can you properly gauge whether reports and data is satisfying the role in a company? At least from the opinion of the Explicit Measures Podcast, the basis starts with the ability to provide value, trust, and insights to an organization through their data. Starting with this as the end-goal, a BI Team can and must strategize on translating success into measurable targets. Let’s break this out into three distinct elements of success, with examples of KPIs for a BI Team.
Elements of Success
Adoption
Adoption has become a buzz word in our industry over the past few years, and with good reason. One could make the argument that the ability to drive adoption should take higher precedent than some of the reports themselves. For reference, we are defining adoption as the maturity, growth, and reliance an organization has on their data via Power BI.
Value / Time
While most BI professionals do not directly create revenue, there is no question that there is a cost. With an ever increasing workload and requests for our time, the ability to validate and choose to work on impactful and value-added reports is essential. If a pro is working on one report, there are five others that are being ignored. Further, are the reports that are being developed and deployed providing the expected insights and information to an organization?
Data Quality
Anyone who has worked in Business Intelligence can tell you – once teams lose trust in the data, it is an awfully long and difficult road to gain it back. If users cannot trust the data in Power BI reports, that both reverts adoption and users will find other means to get their data. BI teams must be able to monitor how up-to-date published reports are, and ensure that the content that is available is current and accurate.
Examples of Success KPI’s
The following are examples of what a Power BI team or Pro can use to evaluate their own success based the pillars of Adoption, Value, and Quality. This is by no means an exhaustive list – this is an amazing community that consistently contributes new and innovate ideas – however there is no current standard for a BI Team success KPIs.
An Example BI Team Scorecard using the new Goals in Power BI
Adoption – KPI’s
Rolling 14 Days / 30 Days Report Views
Just with a basic department metric, simply looking at the aggregate does not create a KPI. While Report Views are important, giving context to the current performance transforms how you view this. This KPI not only shows you your top reports on a 2 week and month period, but also compare with the previous 14 / 30 day period.
Viewing Report Usage on a 30 Day Rolling Basis
Active Users (Weekly, Monthly)
The relationship between the number of Report Views and Users may not be as straightforward as you think. Keeping watch of engaged consumers should occur on a weekly and monthly timeframe. For this, you can simply use a filter on a minimum of X reports viewed per week or month. Depending on your data, you can gauge the current state.
User Distribution by Report
Do not be fooled by high usage numbers in your reports alone! By this, make sure you can identify power users who are hoarding the majority of views for a given report. For example, a great technique to understand this is using the Pareto Principle, or the 80/20 rule in your report views. For example, for your top report, try to track the 20% users, and how much of total views they make up for an entire user base.
SAT Scores, Feedback
The majority of the KPIs in this article focus on quantitative metrics. However, there should attention to create subjective feedback in Power BI. For example, creating a Power BI Feedback Survey can create high value. In regard to when to send out a Survey, the following scenarios are suggested:
45 Days after New Reports Launched (per-Report Feedback)
Quarterly Feedback Surveys (Overall experience using Power BI)
Collecting this data via Power Automate and integrating into Power BI becomes a powerful tool.
Using Customer Voice to Send out Report Feedback Surveys using Variables for Report Name
Value / Time – KPI’s
New Reports Launched
Like Supply Chain Management, ensure you can track newly published reports. Bear in mind, this is not a growth target. There should be some range depending on the size of the BI Team that should aimed for. For example, a consistent small number may show a backlog. However, to high of a number may be saturating the overall experience for users.
New Report Usage
In parallel with tracking newly published reports, keep an eye on the immediate interest from consumers for these new reports. Like with the New Reports Launched KPI, depending on your team and size, decide on a sweet spot regarding range of views you expect. Likewise, have a filter on this based on the date the report was launched, looking at 30 to 45 days forward. The only usage metrics that should be included are ones based on the date the report was published.
Report Lifespan
This is a favorite. Too many times has a BI Author worked on what was deemed an urgent report, imperative to the business. These types of projects involve stress, pressure, and most importantly time taken to get right. Despite this, some of these reports seem to lose their luster once completed, not to be heard from again.
In short, the ability to understand the durability and longevity of reports is essential. This can be taken both from viewing at an individual report level or an aggregate of newly launched reports. Are the reports being built showing value to consumers, not just once, but giving them a reason to return to the report on a consistent basis?
Data Quality – KPI’s
Report Refresh Rate
An obvious choice when referring to Data Quality, if your reports are consistently failing that causes multiple problems. For one, consumers are not receiving the most current data. Secondly, this should trigger within the BI Team an alert that a data model may need to be reviewed for best practice standards.
What is the target rate? While there is no current industry standard, targeting anything near the 95% rate should not be over achievable.
An Example of Report Refresh KPIs
Days Since Report Views
From a bird’s eye view of all the reports in an organization, flagging unused report becomes an actionable KPI. In addition, mapping this to also track duration on a per-user basis provides a wholistic scorecard to future decisions. Firstly, Reports with consistent low Days Since Views should be treated with extra care if any updates are needed. On the other hand, Reports that have not been viewed in over 2 weeks may indicate loss of interest. Depending on the report, a BI Team can decide either to re-promote a report or assess if a report is not providing the value it should.
From the User perspective, tracking Days Since Views by User can provide value in multiple ways. For instance, Users who are “top customers” (i.e. those who overall and per-report have low Days Since Views) tell Authors who to reach out to or who knows what can enhance reports in the future. By contrast, Users with high Days Since Views provide the ability for push-back for requests for new builds. For example, any colleague that may be requesting the most report builds but do not return to their reports give support to Project Managers that this may not be worth the value.
Flagging a Report with 40 Days since being viewed by User
Reports Retired
As we discussed monitoring how many Reports have been launched, what about Reports on their way out? That is to say, how many reports have been removed from the service and from the “public” view. The importance of keeping track of this KPI is all about quality for the consumer experience.
Ensuring that any data published for an organization is current, has a clear objective, and provides clarity is paramount. Above all, this grows the trust and reliance on using Power BI for users. From a discovery standpoint, there is no confusion on reliable data.
Taking the previous KPI (Days Since Views) into account, a BI Team can create a view to monitor “at-risk” reports. For example, any Report with over 45 Days Since Views should be strongly considered to be retired. Any report that meets the threshold should alert users on a pending retirement date. If there are no objections, then these reports should be moved to an Archived workspace.
Getting the Data from Power BI
This may be obvious, but a prerequisite of creating and using KPI’s is having the data. So where is this data coming from? If you are a Power BI Administrator in your tenant, you can import the data via PowerShell. Install the Power BI Module in PowerShell using the following command:
Install-Module -Name MicrosoftPowerBIMgmt
Once you installed the cmdlet, you can use the following script to pull in usage day (by day) into a specified folder on your PC.
Login-PowerBI
## $a is the starting Day. Start with the you want it run and subtract 1
$a = 17
Do {
"Starting Run $a"
$a
$a++
$ab = "{0:00}" -f $a
"Running Day $a"
$daytype = "$ab"
## Update monthly the 05 for start date for the current month
$startdate = '2021-05-' + $daytype + 'T00:00:00'
## Update monthly the 05 for end date for the current month
$enddate = '2021-05-' + $daytype + 'T23:59:59'
$activities = Get-PowerBIActivityEvent -StartDateTime $startdate -EndDateTime $enddate | ConvertFrom-Json
## Update the 05 with the current month
$FileName = '2021' + '05' + $daytype + 'Export.csv'
## Add where you want the files to go
$FolderLocation = 'C:\Users\PBIActivity\'
$FullPath = Join-Path $FolderLocation $FileName
$activities | Export-Csv -Path $FullPath -NoTypeInformation
## Change the number for what day of the month you want it to run until
} Until ($a -gt 19)
The script above collects activity data from your tenant and creates a CSV file per day. Note that this can only go back 30 days – make sure you run this on a weekly basis and change the variables. To learn more about what else you can do with the PowerShell cmdlets for Power BI, read the announcement from the Power Blog here.
The KPIs outlined should serve as a starting point to monitor performance. Power BI Pros without insight into their own performance are stunting their own growth. Not only are metrics for Pros essential for an organization, but it alters the way new reports are built in the future.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Welcome to a new podcast from PowerBI.tips, Explicit Measures. We aim to discuss relevant topics and thoughts around Power BI. Join us Tuesdays and Thursdays at 7:30 am CST (-6 UTC) on our YouTube Channel and subscribe on Spotify Podcasts.
Answering the Why
For most of you who casually visit or frequently visit PowerBI.tips, we deal with many tools, features, languages, and situations in modeling, visualizing, and distributing our data and reports. We are the Power BI Power Users, capable and responsible for building complex solutions. We have to be continually acquiring new skills, and improving on our processes & standards.
Additionally there are so many resources available out in the Power BI Community. So many amazing champions and industry leaders who share their knowledge and expertise on how to build faster, better, and more reliable reports. As a developer we increasingly tasked with, How to audit your data? or how to create complex calculations? We learn best practices and better workflows to implement in our daily tasks.
One aspect of what we do that is not easily discovered in the community, the why of what we do. The why of building reports is universal across all Power BI Pros. As professional BI developers we know the how. If we don’t know the how, we learn the how. This begs the question about the why? Why this particular feature, tool, or product integrate with my organization or my team? Why would my users need this? What does this mean for me?
The why is the question around every BI pro’s water cooler. You may ask this yourself. Possibly sitting at your desk after a meeting, or as you engage with the Power BI community, or User Group. No matter how you get to it, we all face common questions. Eventually all of us will need to ask these types of questions.
Today this is why I am excited to introduce the new Explicit Measures Podcast, available every Tuesday and Thursday live at 7:30am CST.
The Background of the Podcast
Being a Power BI Author
I have been part of the Power BI World since it was “Power BI Designer”. As soon as I was able to download the first application version of Power BI Designer, I was hooked. I felt it was intuitive, complex enough, and just worked. At the time, our organization was vetting new BI platforms. I strongly made the push that we adopt this new tool.
I decided to put all my eggs in the Power BI basket. Believing this tool could easily be widely adopted as it’s part of Office 365, everyone has Office 365. The barrier to entry was low, and we already have strong community. A community of Excel gurus, Power Query and Power Pivot experts.
Over the coming months, we moved Excel files to Power BI, and eventually became a Power BI shop. I soaked up any and all information and resources on how to create DAX measures. Learning what the heck Filter and Row context where. Then figuring out how to create my own function in Power Query. This process was love at first sight. It felt that I was bringing advanced data solutions to my users. I was able to create models and create relationships that otherwise would not have been able to exist. Created tables that finally bridged so many gaps in the data.
I would jump in my chair when a new Desktop version was released. Any new feature that caught my eye would immediately be something I wanted to integrate into my reports. Drill Through (I think at the first Data Summit before being MBAS?) feature was a game-changer to me.
The problem was what I thought was a game changer, which they are. This same excitement did not translate among colleagues who did not focus on data or felt overwhelmed by data. Many of our old Reports were built in SSRS, and users liked them for what they provided.
Excitement vs. Expectations
Drill through, interactive visuals, and other complex features that were in these Power BI reports became overwhelming to users. Not only that, but any data analyst was also now working in Power BI. They were building their own reports, with their own filters, and their own business logic.
What arose from these implications was the matter of users losing trust in the reports. They lost trust in the data they needed to rely on. A department with one report would complain about a certain KPI being too low, while the defending department claimed their report was provided a more adequate number. Users did not know what reports to use, much less how to use drill through. They wanted what would provide them the value they needed.
This brought me to an epiphany of sorts, or multiple over numerous situations. Not only about the importance of governance and adoption in Power BI, but at the end of the day, why do we do what we do? Why are we in this space, and what are we ultimately judged and measured on showing success and real impact at where we work?
Focusing on the Why
This brings us back to the Power BI Water Cooler. How many of us have delt with these sort of situations, problems, and trying to find a solution? I would put good money on the majority of us who have been working with Power BI for a while have gone through this type of arc.
In conversations with other User Group Leaders, the community, and other Microsoft MVPs. I have learned time and time again this is not a siloed story. What can really separate a Power BI Tech vs. a Power BI Pro is the ability to think of alternative solutions. What is the ultimate impact of any feature, product, or visual on the most important audience, the consumer.
We must think this way. We must be able to process all of the new capabilities that come out at rapid speed. Then understand who our consumers are. Finally, understand of not just Power BI but the data, and where can we further drive more and more trust into the data.
Being Explicitly Measured
I want all of you whom this article may hit close to home to join us in this ever-going discussion. Having the pleasure of knowing Mike and Seth of this site has shown this need is so prevalent. We want to bring to the surface these questions, topics, and discussions. There is more than one way to define a measure, but the importance is that you start with some definition.
That is truly where the name of the podcast, Explicit Measures, comes from. Being able to start with a use case (take the technical situation of defining a measure), understand what is available to you (the functions), and what is best to apply (FILTER inside a CALCULATE, ALL or ALLSELECTED?).
The Explicit Measure Podcast is meant to be first entertaining for users. We all need an outlet for some of the frustrations we feel by end users, and being with fellow users who understand the pain helps the feeling you are not alone!
The heart is in the ability for us to debate, argue, and most of all inspire. Find solutions, figure out what impacts us and where can we go from here.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Power BI Version Control is a free, fully-packaged solution that lets users apply version control, local editing, and manage PBIX or PBIT files. The solution runs entirely on Power Apps (Power Platform) and SharePoint. Power BI Version Control can give business users or smaller organizations the ability to easily implement and utilize version control for their Power BI projects.
Note: updating the app version will require you to re-import the SharePoint connection and folders.
In most version control systems, branching is a method to make edits to code in a safe and reliable way. Typically, users “branch”, or copy, the code to their local machine to make edits. They can then “merge” the code back to the master code, adding comments of what has changed and who changed it. Each change is saved as a different version, with the ability to go back to any version. Small, frequent changes are helpful, making it easy to undo any errors. This type of version control requires that every file be saved in a plain text format, so the differences between two versions of the same file can be easily identified, cherry-picked, merged, etc.
However, unlike pure source code, Power BI reports are packaged into PBIX or PBIT files, which cannot be compared against each other in the way we just described. This makes it much harder for multiple users to work on the same set of files simultaneously. While it is possible to use Azure DevOps, GitHub, etc. as a version control solution for Power BI reports, it’s difficult to setup and use (especially for non-technical business users). The Power BI Version Control solution bridges that gap by harnessing SharePoint’s built-in file versioning and the user-friendly UI/UX of Power Apps.
Why use Power BI Version Control?
Single shared location for reports (no emailing files!)
Keeping all of your Power BI report files in SharePoint means that you’ll always know where to find them, and that they’ll always be the latest versions of those files.
Keep all versions of the report (no adding numbers to file names!)
We often want to keep files from the past in case we need to roll back changes. Instead of adding version numbers or initials to the file names (like Sales_Report_v2.5_Final(1)(1).pbix), SharePoint will keep all versions of your report files automatically. Additionally, by using this Power BI version control method, it is possible to roll back to any of these versions whenever needed.
Ability to check out files exclusively, like a library – only one person can make changes at a time
When working in teams, you may have multiple people working on a project (see this post on Power BI team members). If you have more than one person who may edit a file, we want to make sure they are not trying to do it at the same time. Power BI Version Control ensures that only one person can check out any given file at once. As a result, nobody else can make edits to a file that you are working on. No more conflicts or working on outdated versions of files!
Ability to check-in files – add comments describing changes made since the last check-in
After making edits, we want to be able to keep comments about what was changed. With each version we are able to add a description of what has changed since the last version.
Work locally – make all changes on copies, so we do not edit our files directly
Another important benefit of Power BI Version Control is that we always work on copies of our reports. We can save and experiment as we work on the files, knowing that we will not accidentally damage a live report. We do all work locally on our machine and separate to our production or live reports. If needed, we can discard all changes and start again.
How to use Power BI Version Control
Power BI Version Control – Installation Instructions (YouTube)
Installing the App
Download the Power BI Version Control app solution file (from the link near the top of this page)
In the list of Solutions, find Power BI Version Control
Click the ellipsis and select edit
The app will now open in edit mode
Add the SharePoint folder by following the following steps:
Open the data sources tab
Select Add data
Type “SharePoint” in the search bar
Select SharePoint (note: be careful not to select “SharePoint Sites”)
Choose the SharePoint connection you selected earlier
In the pane that opens, enter the URL of the SharePoint site. This should be in the format: https://DOMAIN.sharepoint.com/sites/SITENAME
Click Connect
Choose the correct Document Library and click Connect
Select the tree view and navigate to the Settings Screen
Fix any red X marks on the page by updating the settings to match your folder structure (as described in the next section). If you used the default folder and document library names you should not need to update any settings
Setting the connections
There are four numbered blue boxes that may require updating. If you see any red X marks next to any of the following boxes, click on that box and update the required property.
1.
Click this box and make sure Items is selected. Update the text to match the document library name (this is also the name of the data source you imported earlier). Use the IntelliSense (auto-complete) to ensure the correct value is selected. The text should turn green when correct, and the red X should disappear.
2.
Click this box and make sure OnSelect is selected. The formula should read Refresh(‘[Your Document Library]’). Update the text to match the document library name (this is also the name of the data source you imported earlier). Use the IntelliSense so ensure the correct value is selected. The text should turn green when correct, and the red X should disappear.
3.
Click this blue box (not the label) and make sure Text is selected. Update this to the checked out folder name, surrounded by double quotation marks.
Note: if you add a forward slash to the end of the folder name, this will add all checked out folders to the same folder. Otherwise, a sub-folder will be created for each user based on their user ID.
4.
Click this blue box (not the label) and make sure Text is selected. Update this to the published reports folder name, surrounded by double quotation marks (e.g. “Published Reports”).
Check that there are no red X marks. A red error message will also show at the bottom of the screen if there were any errors in setup. Resolve all errors before publishing.
Click File then Save
After the app has saved, click Publish to ensure all changes are deployed
Using the App
Power BI Version Control – User Instructions (YouTube)
We recommend embedding the Power BI Version Control app in a Teams channel. Additionally, the SharePoint site can be added to the Files section in Teams. This will allow all appropriate members to access the Power BI Version Control app and report files in one place.
Check Out
Open the Power BI Version Control app
Click the Check Reports Out button on the Home Screen
The Check Out Screen will list all PBIX and PBIT files in the Published Reports folder. Select the reports you wish to modify in the Check Out Reports column. You will only be able to check out reports that are not already checked out
Click Check Out Reports button
Wait a few moments for the reports to process. It may take longer if using large files
Editing the files
Navigate to the OneDrive folder on your local machine. The selected reports will appear in the synced folder Checked Out Reports (or sub-folder)
You can now open and edit these files. If using live connections, consider using the Hot Swap Connections Tool
If you want to save copies, you can do so in a sub-folder or elsewhere on your local machine. Avoid this when possible. We recommended to make small and frequent updates / check-ins
When ready, make sure only the files that are ready for check-in are saved in Checked Out Reports (or sub-folder). Make sure the names of files have not been altered
If you manually publish reports, publish immediately before closing and checking in
Check In
Once edits are done, Check In the reports from the Checked Out Reports folder to the Published Reports folder. Alternatively, you may wish to discard your work. As a result, this will release the file and ignore any changes you have made. Next, it will delete the file from the Checked Out Reports folder.
Commit changes:
Open the Power BI Version Control app
Click the Check Reports Out button on the Home Screen
This will list all PBIX and PBIT files in the Checked Out Reports folder. Select the reports you wish to Check In in the Check Out Reports column. You will only be able to check in reports that are checked out to you
Make sure to add comments. Include details on changes you made. If using Azure DevOps, Planner, or some other project management tool, include the relevant ticket/task number(s) in your comments whenever possible
Click the Check in reports button
Discard changes:
Navigate to the Check In page by the button on the main page
This will list all PBIX and PBIT files in the Checked Out Reports folder. Next, select the reports with changes that you wish to discard in the Discard Report column. You will only be able to discard reports that are checked out to you
Confirm Discard
Helpful Tips
Also included in the app is a flow called Initial Step: Create Power BI Reports Library with Folders.
Open the flow and select Run
Paste in your SharePoint site where you wish to create the folders, site e.g. https://powerbitips.sharepoint.com/sites/powerbi
Running this will automatically create a library and folders in your desired site. It will use default names, which means you will not need to configure the app in the settings page after connecting to the data source.
Sync Folders
You should sync the Checked Out Reports (or sub-folder) that was created to your local machine’s OneDrive, allowing for local edits. If using sub-folders, you must check out a file once to create the folder.
Ensure to sync only theChecked Out Reports folder (or sub-folder).
You can sync either through SharePoint or through Microsoft Teams.
From SharePoint:
Navigate to the correct SharePoint site
Select Documents and navigate to the Checked Out Reports (or sub-folder)
In the toolbar, select Sync
From Teams:
Open the Teams channel
In the toolbar, select Files and navigate to the Checked Out Reports (or sub-folder)
You can now access the files in this directory from the local machine.
Limitations and Scope
Known issues:
The app will fail if the internal SharePoint name and the Display name do not match. This happens when a SharePoint site is created, and the display name is manually changed later. We are working on a patch for this.
If you have a very large number of reports, the app may not display all of them. We are working on a patch for this.
Design:
This solution is designed to handle thin report files, and not large models. The solution will copy and publish to one single folder, and is not intended to manage environments (e.g. dev / test / prod). It should be connected to a Development environment, we recommend using Power BI Deployment Pipelines to deploy reports from Dev to Test, and from Test to Prod.
The Power BI Version Control app solution performs these main tasks:
Check out and lock editing to a single user
Copy files to a local synced folder for safe editing
Keep version history and enforce developer comments on each check-in
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Power BI took the BI world by storm 5 years ago, there has been a release of the Power BI Desktop almost every single month. The infant that was Power BI has grown into a fully grown behemoth of an enterprise tool. It covers the spectrum of data access and storage options. It has internal and 3rd party tools that provide capabilities that are second to none. Top that off with its flexible licensing and implementation models within organizations and it is no wonder it is in front of the pack. As a result of that pace and growth, the amount of knowledge and skills required to build, manage and implement Power BI has also grown significantly. This has resulted in “Power BI”, meaning a lot of things, to many different people. Today we aim to solve that problem by introducing the Power BI Skills Matrix.
First in a three-part series
This is the first post in a three-part series. It is meant to help people make an honest assessment of their skills. The intent is to enable everyone to be able to clearly articulate their skills to themselves and others. In addition, this content can also be used by hiring managers and recruiters as a litmus test to better engage with individuals around their Power BI technical skills. The second post will focus on techniques for honing skills. The third will focus on methods or behaviors that will help people sell their skills and themselves in an interview
The aim of this series is to bring some clarity around what everyone can, and should, expect from individuals who work with “Power BI”.
Focus Areas
A number of job requirements and focus areas rely heavily on one or more areas of Power BI. Meanwhile, the generalization of the term “Power BI” in conversation and in job descriptions can drive confusion. An interviewer may only be focused on one skill or another. It is good for everyone to understand the entire scope of skills. Then we can collectively understand what might be expected in a particular role. If you have an interest in what those specific role areas might be, check out Steve Campbell’s blog here where he dives into some of those specifics.
During the last 6 months, I’ve been extensively interviewing for Power BI Developer and Data Engineering roles. I’ve had the pleasure to speak with a wide variety of candidates across the United States. We ran them all through the same battery of questions and have discovered some consistent experiences. I believe this series is a great way for me to share my thoughts on what sets apart the great candidates from those I move past in regards to their technical skills.
Rate Yourself
To begin with, you need a litmus test, so we’ll start with a little exercise. Think about your level of expertise in Power BI and rate yourself from 1 to 100. 1 – you just heard about Power BI 100 – You are a Power BI Master
What did you rate yourself?
I’ll describe the general skills that align to each of these ratings. After that, we’ll see if what you have in your mind matches the output of the Skills Matrix. I designed a simple grid of 10 increasingly difficult skills in 5 different areas. Each cell is worth 2 points and there are 50 cells, therefore a total of 100 pts. Each column can have a max point total of 20. The more points in a skill column, the stronger you are in that particular area. This should correspond to specific roles you would be a good fit for. As a result, the more overall experience you have, the higher the overall score.
Each of the skills in a rating will build on the previous level, the skills represented carry forward to all other levels. For instance, if you reach row 5 for Model, you know you already have 10 pts for that column.
There is a little room for interpretation as Enterprise skills in Connect/Transform may be in other tools. Its the core progressions that count the most in terms of expertise.
Power BI Skills Matrix
Power BI Skills Matrix (To see a large version – Right Click and Select Open Image in New Tab)
Now, let’s go through the exercise of rating yourself again. You should be able to clearly see which skills you are strongest in and what your overall knowledge (score) of Power BI would be. Did it match your initial thoughts?
Be honest with yourself and your skills. One of the worst things you can do is over promise your skills on a resume. Because when it comes time to deliver in a technical discussion, you will likely fall short.
The Power BI Skills Matrix above provides a straightforward way for individuals to quickly identify strengths and weaknesses from a technical standpoint. There are a wide variety of jobs that may focus more, or less, on the use of these Power BI areas. The first step in getting into any of them is to accurately understand and describe your current skills. The next post in this series will build on this matrix where we’ll recommend next steps. Focusing on learning and improving areas that you might be weak in is key in setting yourself up for success. I hope the community at large finds this useful, and if you have any recommendations for updating or enhancing our matrix, please feel free to let us know.
If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
Melissa Coates of Coates Data Strategies has produced a new version of the Power BI End-to-End architecture document. If you haven’t seen this document it is definitely worth a read.
Summary of Changes
If you’d like to watch a quick webinar of the updates to the document you can watch it here.
Twitter Announcement
Here is the original announcement and release via twitter.
If you’d like to download the latest document you can find it here: https://www.coatesdatastrategies.com/diagrams
Note: The diagram can be downloaded as a PDF and as an Image.
How it started / How it’s going
If you are on social media at all you will see meme’s about How it started vs. How it’s going. In the thread of Melissa’s tweet we have a fun image showing the progression of Power BI End-to-End architectures. The number of changes since 2015 is absolutely stunning. Power BI has grown up a lot since 2015. That being said, I’m greatly encouraged to see the Microsoft Power BI team pushing amazing new features.
Learn More about Power BI End-to-End
A one page document can contain a lot of information about Power BI. However, it can’t capture every aspect of Power BI. Thus, if you’d like to learn more about Power BI and it’s Administration, Please check out Melissa’s Training. The Power BI Development and Governance Workshop is a deep dive for users who need to administer Power BI.
We hope you enjoy this great document to use or share with your team. I’m confident that you will find areas of interest or learn about new ways to leverage Power BI.
Special thanks to Melissa Coates for participating in the quick webinar around the Power BI End-to-End architecture.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
I was recently contacted by Power BI Community member Gomathy Viswanathan and Ashwini Nayak. Thus, it is my pleasure share with you their incredible report. The Harry Potter Escape room. If you are as curious as I was you will enjoy this fun and light hearted puzzle.
Play the Game
Take a little break today and give the Harry Potter Escape room a try.
The Creators
Escape Room, Harry Potter Edition was originally posted on the Power BI Community. Be sure to follow Gomathy and Ashwini on LinkedIn. For a quick tutorial on how to use the game the creators watch the video below.
Download the Game
Feel free to download a copy of the Escape Room to see how it was built. While these games are fun to play, there are many techniques you can learn from. Learning the ins and outs of bookmarking definitely will help your report building skills for example.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
This tip outlines an easy deployment method for data tables that have been manually added to a model via “Enter data” in Power BI Desktop. This is a very effective method especially as it relates to large model deployments. The scenario is typically seen when you build data tables to support slicer/measure interactions. As those selections are made, each one of the values will generate a different DAX calculation to drive a different insight.
An example of that setup would look like this.
Create a data table
First, manually create a data table for slicer selections.
Create Measure and Slicer
Create a Measure that uses separate calculations ,and reference the data table in our slicer to determine which measure we want to display.
As seen above, the slicer has the value from the manually entered data. The id’s correspond to the measures you will want to display in the report visual.
This pattern gives an amazing amount of flexibility to use the same visual and look at different metrics within the same visual. This removes the need for bookmarks and more visuals for each calculation.
Typically, I deploy all my metadata changes via ALM toolkit. (I love that tool). However the slicer options and calculations in this method have a data element that needs to be refreshed as well. Which suggests I need to refresh the data model with these types of updates. However, I don’t want to refresh the entire model. So, I opened up my connection to my model in the Power BI Service to investigate.
You can connect to your Power BI workspace Analysis Services models by following these steps.
Connect to Analysis Services Model in Power BI
First ensure you have the XMLA endpoint enabled appropriately. This is under Settings > Admin Portal > Capacity Settings > (Select Capacity) > More Options > Workloads
Once we have the capacity setup up. Copy the link from your Premium Workspace. Click on the ellipses next to the workspace and select Workspace settings.
Under Settings Copy the Workspace Connection
Open SQL Server Management Studio and apply the connection string in the Server name: location. (Remove the ending of “Initial Catalog” if it is present initially. Set the Authentication to AAD-Universal with MFA and enter your user name (email address) in that section.
Locate the Code
After getting hooked up, my “There has got to be a way” hat went on and I started digging into the objects in SQL Server Management Studio (SSMS).
Mike and I noticed that the entire Power Query query is part of the connection within the table definition. Part of that query is the binary string used when a manual data table is created. Could it be, we could update the local PBIX, grab that binary code and replace the binary in this table!? “YES!”
Here is how you do that.
Right click on the table name > select “Script Table as” > select “CREATE OR REPLACE to” > select “New Query Editor Window” (SSMS hates screenshots apparently, so I could not snag them)
Now that you have the query open, scroll down till you see your Power Query code. Search for the very begining where you see the binary for your data table. Replace this binary string with the new string you have in your PBIX file.
Pay close attention that the binary is wrapped in ‘\’ in the front and before the double quotes at the end. You have to keep it that way or it will break your code.
Execute the query
Refresh your Power BI dataset table
Right click and select Process Table to refresh the data in the table. Choose Process Data
BAM! You’ve just updated the table in the Service without the need to refresh the entire model!
As a result of these findings, I’m very interested in what other parts of the Power Query connections we can update/modify. I think I’ll be exploring those further in the coming months. I am really excited that Power BI has moved onto the Tabular Object Model and we now have the ability to use XMLA read/write. There is so much flexibility and speed in our deployments now! We’re looking forward to finding new and interesting ways to push changes without the data. We hope you find this tip useful, thanks for reading.
Requirements for the above tutorial to work:
Be using a Premium Capacity
In your Power BI Desktop, enable the enhanced metadata format
Toggle on the XMLA Read/Write in the Power BI Service (described above)
If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
Splitting models from reports has great advantages, but can make it harder to edit. When editing a model it is useful to see how you it will effect the reports. Using live connections would mean republishing the model back to the cloud and then refreshing the connection for every change you wish to test. In addition, you would probably want to make test workspaces to not overwrite a live production model while developing.
Now there is an external tool that can help solve these issues. The tool has two functions. The first will allow you to switch from a live connection to directly connecting to an open Power BI report. This will allow “Local Development” so that it can be done on your machine without needing to republish. Changes can be seen instantly and time spent on testing can be dramatically decreased. The second will removed any connections to allow to reconnect to a shared dataset or AAS model.
After installing the tool, click external tools the Hot Swap Connections to launch.
Connect Tab
This tool will remove any live connections from the selected report and connect it directly to the Power BI report it was launched from. This will only remove live connections so you cannot accidentally delete entire models.
You can choose between Overwrite and connect or Copy and connect. Selecting Overwrite will directly edit that file by removing the connections and replacing with a live connection to the current file. Selecting copy will leave your file intact and create a copy in the same directory with the suffix defined in the settings tab. It will then open the report that is connected to the model file.
Steps:
Open your Model file
Select the Connect tab
Run Hot Swap Connections
Choose to Overwrite or Copy
Select Report file to connect
Remove Tab
This tool will remove any live connections from the selected report and open the file. This is useful when you have made local edits and want to connect it back to a dataset or analysis services model. This will only remove live connections so you cannot accidentally delete entire models.
You can choose between Overwrite and remove live connections or Copy and remove live connections. Selecting Overwrite will directly edit that file by removing the connections. Selecting copy will leave your file intact and create a copy in the same directory with the suffix defined in the settings tab. It will then open the report that has no connections.
Steps:
Open any Power BI report
Select the Remove tab
Run Remove Connections
Choose to Overwrite or Copy
Select Report file to remove connections
The script will leave all visualizations and report features intact. But, all connections will be removed. When you open the report again in power bi desktop, all visuals will appear broken:
This is because you have removed all data from the report. Select a new data source to connect the report to. If the new source matches the names of the columns and measures used in the visuals, they will all repopulate.
Settings Tab
When selecting Copy and connect or Copy and remove live connections, the tool will create a copy of your report first so you do not directly edit you report file. It will place the copy in the same directory as the original and add a suffix as defined in the settings tab.
Watch the webinar below
Steve and Mike talk through the external tool and see it in action!
