The Business Ops tool is designed to simplify your Development Experience with Power BI Desktop. There are a lot of challenges remembering where all the best power bi external tools are stored. Many MVPs contribute amazing projects to make your development experience better. The installer is intended to streamline and increase your efficiency when working in Power BI. Download this installer and you can add all the best External Tools directly into Power BI Desktop. Our release includes all the best External tools for Power BI Desktop. This will enable you to have a one stop shop for all the latest versions of External Tools.
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 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:
To say this another way. Not all columns have datatypes in DAX, specifically speaking to using dynamic ranking with an “Other” category. Let me explain.
The Tabular model is comprised of tables with typed columns. This is a truth learned in one’s Tabular infancy. Whether the table is imported, calculated, or Directly Queried does not matter; all tables have columns with explicitly defined types.
DAX is the language of the Tabular model. It has less type discipline than tables in the Tabular model. Measures can have a Variant data type. Implicit type conversions abound. Nor are DAX expressions limited to returning scalar values. DAX is a query language. An expression may return a table. When defining a table in DAX, projected columns have no explicit type. Each row’s value is calculated independently and its type depends on its value. This is the phenomenon we seek to explore in this post. If this seems unsurprising, you can probably stop here.
A practical scenario
Here is the scenario where I discovered this. I was helping a colleague with a dynamic ranking measure and aggregation. The requirement read:
Show the top for production plants as ranks 1 through 4. Group all others ranked 5 or lower together as “Other”.
We have three tables:
a fact table 'Fact'
a dimension table 'Dim', related to 'Fact' with the relationship 'Fact'[DimKey] <-N:1- 'Dim'[DimKey]
a disconnected table, 'Rank Selector', with the values: “1”, “2”, “3”, “4”, and “Other”.
We started with a measure [Agg] as our base measure.
Agg = SUM ( 'Fact'[Value] )
[_Rank (variant)] is a helper ranking measure, returning a rank 1-4 or “Other”.
_Rank (variant) =
VAR rawRank = RANKX ( ALL ( 'Dim' ), [Agg],, DESC )
// note this value is either a number 1-4 OR the string, "Other"
VAR groupedRank = IF ( rawRank <= 4, rawRank, "Other" )
RETURN
groupedRank
Below is a screengrab of these two measures evaluated against our 'Dim'[Plant]. As you can see, [Agg] returns a value for each plant. The top four plants have ranks of 1, 2, 3, and 4. The other plants all have the same rank, “Other”. This is all as expected.
'Dim'[Plant] with [Agg] and [_Rank (variant)] measures, working largely as expected. Each dim value has a value for [Agg] and a rank of 1-4 or the string, “Other”.
The issue reared its head when we attempted to use these ranks to define a new visual based on 'Rank Selector'[Rank]. We defined a measure to show [Agg] based on the plant’s rank (1-4 or “Other”), rather than the plant name. Note that 'Rank Selector'[Rank] is a column in a data model table, with Data type: Text. Our expectation is that we will see a table like the image below.
A table visualization showing six rows with labels: “1”, “2”, “3”, “4”, “Other”, and “Total”; and showing an aggregate alongside each.
Wherein our heroes encounter a challenge
[Ranked Agg (variant)] is the display measure to show aggregations against 'Rank Selector'[Rank]. We wrote the variant version you see below first, not realizing the perils that laid ahead. (Correct implementations follow later.)
Ranked Agg (variant) =
VAR rankContext = VALUES ( 'Rank Selector'[Rank] )
VAR ranked =
ADDCOLUMNS (
ALL ( 'Dim' ),
"@rank", [_Rank (variant)],
"@val", [Agg]
)
VAR currentRank = FILTER ( ranked, [@rank] IN rankContext )
VAR result = SUMX ( currentRank, [@val] )
RETURN
result
When we visualize this measure against 'Rank Selector'[Rank], we get the result below.
A table visualization showing only the 'Rank Selector'[Rank] value “Other” and “Total”. Specifically, we do not see any value for ranks “1”, “2”, “3”, or “4”.
A table visualization showing only the 'Rank Selector'[Rank] value “Other” and “Total”. Specifically, we do not see any value for ranks “1”, “2”, “3”, or “4”.
What is going on here? We see data only for the Rank Selector'[Rank] label “Other” but none of the numbers. Let us walk through the measure step-by-step to understand this result.
VAR rankContext = VALUES ( 'Rank Selector'[Rank] )
rankContext captures whatever the current filter context is for 'Rank Selector'[Rank]. This is one or a collection of the values: “1”, “2”, “3”, “4”, and “Other”. For a detail row of a table visualization, the context contains exactly one of those values. For a total row (assuming no other slicers or filters), the context contains all of those values.
VAR ranked =
ADDCOLUMNS (
ALL ( 'Dim' ),
"@rank", [_Rank (variant)],
"@val", [Agg]
)
ranked is a table with all rows and columns of the data model table 'Dim' and two projected columns, [@rank] and [@val]. For any row, i.e., for any specific plant, [@rank] is one of 1-4 or “Other”. Many rows may have [@rank] = "Other". We do not expect ties, so there are exactly four rows with numeric ranks, one each for the ranks 1-4. (This table looks like the first table visualization screenshot above.)
The critical part of both the ranking logic and the phenomenon we are exploring is in currentRank. This is a subset of the table, ranked, holding only the row or rows which have a [@rank] value that exists in the filter context captured in rankContext (again, one or a collection of the values “1”, “2”, “3”, “4”, and “Other”).
Note, that we see data only for the Rank Selector'[Rank] label “Other”. As you recall, our [_Rank (variant)] seemed to work correctly above – it definitely returned ranks 1-4, not just “Other”. 'Rank Selector'[Rank] has the correct values. We checked that our logic in [Ranked Agg (variant)] was correct. We verified in DAX Studio that ranked held the correct table. We even got to the point that we checked whether VALUES was behaving as we expected in rankContext. (I will note that if you find yourself verifying the behavior of an expression as basic as VALUES ( 'Rank Selector'[Rank] ), then you, too, may find yourself questioning what has brought you to this point.)
VAR currentRank = FILTER ( ranked, [@rank] IN rankContext )
We continued checking and troubleshooting and identified that currentRank had binary state: either it would be an empty table or it would be a table holding only rows with [@rank] values of “Other”. It would never hold a row with a rank value of 1, 2, 3, or 4. It seemed that the predicate in our FILTER would never find 1 to be equal to 1, or 2 equal to 2, and so on.
How could basic logic be so broken just in this measure? There was much gnashing of teeth. Several head-shaped indents were beaten into the nearest wall.
DAX, the language, has a more permissive type system than the Tabular model
You may have observed some apparent inconsistency in my quoting or not-quoting the rank values above. In fact, I have been scrupulous to always quote values when referring to 'Rank Selector'[Rank], the model column with type Text, and to not-quote the rank values returned from [_Rank (variant)]. The column 'Rank Selector'[Rank] has exclusively string values. The measure [_Rank (variant)] sometimes returns a numerically typed value in the range 1-4 and sometimes returns the string “Other”.
In DAX, 1 = 1 is an equality test that returns TRUE. Similarly, 1 IN {1, 2, 3, 4} returns TRUE, because 1 = 1 and 1 exists in the table there. In DAX, 1 = "1" is an equality test that throws a type error. Numbers cannot be tested for equality with strings. They are different types. Therefore, a number can never be equal to a string. Thus, 1 IN {"1", "2", "3", "4", "Other"} also throws a type error.
The lightbulb moment
In [Ranked Agg (variant)], currentRank has a column [@rank] with type Variant. Sometimes, [@rank] has a value in the range 1-4, type Whole Number. Sometimes it has the value “Other”, type Text. When we evaluate the predicate [@rank] IN rankContext, there are exactly two possible results. Either we ask for an impossible membership test, “Is this numerically typed rank value in the set of Text values?”, or we ask whether the string “Other” is in that set. The first cannot succeed. The second only succeeds for the “Other” rank.
The Fix
The fix, then, is straightforward. We must always return a value of type Text in our ranking measure. Correct measures are below with comments highlighting the modifications.
_Rank (typed) =
VAR rawRank = RANKX ( ALL ( 'Dim' ), [Agg],, DESC )
VAR groupedRank =
// with an explicit typecast, our measure now *always* returns a string-typed value
IF (
rawRank <= 4,
FORMAT ( rawRank, "0" ), // force string type
"Other"
)
RETURN
groupedRank
Ranked Agg (typed) =
VAR rankContext = VALUES ( 'Rank Selector'[Rank] )
VAR ranked =
ADDCOLUMNS (
ALL ( 'Dim' ),
"@rank", [_Rank (typed)], // the only difference here is using our typed rank helper measure
"@val", [Agg]
)
VAR currentRank = FILTER ( ranked, [@rank] IN rankContext )
VAR result = SUMX ( currentRank, [@val] )
RETURN
result
The measures above give us the results we expected all along, as you can see in the table visualization below.
A table visualization showing 6 rows with labels: “1”, “2”, “3”, “4”, “Other”, and “Total”; and showing the correct values of [Ranked Agg (typed)] alongside.
Below is a screengrab of all the measures in visualizations, so you can see the differences.
A screenshot with all three of the table visualizations shown prior in this post: the 'Dim'[Plant] table with [Agg], [_Rank (variant)], and [_Rank (typed)]; the incorrect behavior with [Ranked Agg (variant)]; and the correct behavior with [Ranked Agg (typed)].
Summary
Tabular model tables have columns of explicit type. DAX expressions have implicit types. Columns defined in DAX have Variant type. Each row has an implicit type based on its value. If such a column is a Calculated Column added to a data model table, then an explicit type must be assigned and all values in that column will be cast to that type at load time. When such a column is used as an intermediate step in some calculation, each row may have a different type. If your operations depend on types, you must account for this explicitly. This is especially relevant when operating on data model and DAX tables together.
Insights from the community
When talking about this with some peers, Maxim Zelensky (blog, Twitter) observed that there is no type indication in Power BI Desktop. Nevertheless, you can identify this sort of thing in DAX Studio. The result of a query such as that below will right-align Variant-typed values and left-align Text-typed values. This query references the included Power BI file and its results are shown in a screenshot below. You can observe similar alignment in the table visual shown above. In general, when visualized, numeric data is presented with right-alignment and textual data left-aligned. This practice predates Power BI; it is a general rule that Power BI and DAX Studio wisely adopted.
A screenshot showing the results of the above query, with [@text] left-aligned and [@variant] right-aligned.
Using the sample code and PBIX
Here is a link to download the PBIX that was used for examples above. This has four visuals to demonstrate the phenomenon discussed in this blog post. The first visual is a table with 'Dim'[Plant], [Agg], [_Rank (variant)], and [_Rank (typed)]. Of note in the first visual is left-vs-right alignment of the two [_Rank ...] measures. The second visual is a table with 'Rank Selector'[Rank] and [Ranked Agg (variant)], showing the incorrect results of the measure depending on a Variant-typed intermediate column, with a value only for rank “Other”. The third visual contrasts with the second, using [Ranked Agg (typed)] to show the correct behavior of the dynamic ranking and grouping logic, with a value for all ranks and a correct total. Lest one protest that the phenomenon above may be an artifact of using VARs, the last visual, on the bottom, shows an example that uses no VARs and references no other measures. The behavior of this last visual is identical to the incorrect [Ranked Agg (variant)] version.
This past weekend, I was a man on a mission. There were two pressing reasons for a new release of Business Ops:
The authors of many popular External Tools released updates recently, so we needed to ship a new version of Business Ops with those updates included
Last week, Michael Kovalsky (ElegantBI.com) released a brand new External Tool called Report Analyzer (which we can already tell will be an instant classic), so we wanted to get it out there and into the hands of report developers ASAP
So, I toiled away most of the day on Sunday, cramming as much value as I could into the next version. And along the way, I learned some important new software development skills, including how to:
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 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:
If you have installed Business Ops 2.0 or 2.0.1, you should use the “Edit External Tools” menu to remove the External Tools you have installed, then close and uninstall Business Ops.
In previous versions of Business Ops, its installation path was C:\Users\<Username>\AppData\Local\Programs\pbi-tips-business-ops\, which could be accessed by any process running on the machine. This struck us as somewhat insecure, so we decided to beef up the security. When we released Business Ops 2.0, we changed the installation path to C:\Program Files\PowerBI.tips Business Ops\, which requires elevated permissions to modify any files inside. This change increased the security of Business Ops, but those of you who are familiar with how file paths work can probably guess how it also had some other, unintended consequences. For those who aren’t, here’s what happened:
The installation path in previous versions did not have any spaces in it, and everything was working fine. But when we changed the installation path to one that includes spaces, several of the included External Tools in Business Ops stopped working. This wasn’t immediately obvious because it only affected the handful of External Tools which launch a PowerShell script by calling its absolute path. Once we identified the cause of the issue, the fix was pretty straightforward, and we got everything working again.
Thanks for reading!
James
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:
Replaces the source DataflowID & WorkspaceID on all Power Query partitions in the model
Similar to the previous script, this one is specialized for automatically replacing old DataflowID and WorkspaceID references in Power Query with new ones. Helpful in situations where you need to re-deploy an existing Dataflow and Dataset to a new workspace, and then re-link the new Dataset to the new Dataflow
Useful for quickly excluding specific tables from the model refresh, which you may need to do for any number of reasons, including troubleshooting, performance, etc.
This repo has lots of other useful Tabular Editor Scripts, and we add more every day, so check it out! Also, if you have some handy scripts of your own, you can Fork the repo and submit a Pull Request. Then, we will add your scripts to the collection.
Happy scripting, everyone!
James
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 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:
A few months ago, I was writing and running various PowerShell scripts to manipulate the connected data models in my Power BI Desktop files. During model development, I was constantly having to open DAX Studio to copy the Server:Port connection string, and thinking, “there’s got to be a faster way to do this.”
So, I developed and released a simple External Tool for Power BI Desktop, which copies the Server:Port connection string for the currently-connected data model directly to the clipboard.
I’m a strong believer in modular design, so when I build something, I try to make it do one thing, and do it well. I believe this External Tool for Power BI Desktop is a great example of that philosophy in action.
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 article describes Power BI Bookmarks tips, tricks, and best practices. Bookmarks are a powerful feature that can greatly improve the reader’s experience. However, there are several settings you should be aware of. Used incorrectly, they can become hard to maintain and often not display the intended functionality.
Power BI Bookmarks
This article will assume some basic knowledge on how to record or apply bookmarks. Please see this. If you are new to Bookmarks, you may want to watch this helpful video by Adam Saxton from Guy In A Cube.
It will run through an example of using a switch visuals bookmark group. If you have not seen this before, I suggest you watch this video, also by Guy In A Cube.
The rest of the article will focus on some tricks and tips using bookmarks, while walking through an example of a visual switch between a map and a graph.
You should always update the bookmark settings when creating them. This should always be as specific as possible to the action they are trying to achieve.
Here is a quick breakdown of the settings:
Data: This captures items including filters and sort order. It does not capture if the item is visible or not.
Display: This captures whether an item is visible or not, without modifying filters or sort order.
Current Page: This will switch to the current page view if you apply the bookmark from a different page. If unselected, it will still apply the bookmark, but it will stay on the page you apply it from.
All Visuals: If this option is selected, it captures every setting on the page. This can include items in the filter pane, or even if the filter pane is open. I would recommend to never use this setting.
Selected Visuals: Selected visuals still only apply bookmarks to the visuals you had selected when you record the bookmark. You can select items by holding control and clicking on them in the selection pane.
In general, I try not to use both Data and Display together, as most cases bookmarks are just changing one of these fields. Previously, it was required to use bookmarks to navigate pages. However, this is no longer needed after new functionality allows this to be done with buttons directly. Therefore, I rarely use this feature.
One of the best Power BI Bookmarks tips – use selected visuals only. Recording bookmarks on all visuals often has unintended consequences and can be difficult to manage. Rarely is this needed and can become very hard when adding more visuals to the page.
Rename Your Visuals
The first tip is to rename your visuals. Each visual is given a name, which can be viewed in the Selection Pane. By default, the name is usually the type of visual it is. This means if you have several slicers on a page, you might get a list of several visuals with the same name. While this is not an end user feature, it can make it difficult to identify the correct visual when developing.
We recommend renaming each visual when you add it. First, open the selection pane. Next, double click on the visual you have just added in the selection pane. Rename the visual to something that calls out what it is. Our recommended naming strategy is the following.
Visual - Description
Example of renamed visuals in a selection pane
Pro Tip: When a page contains multiple bookmarks it’s difficult to know what Bookmark touches which Visuals or Groups. Thus, when you are planning multiple bookmarks on a single page add an ID at the end of the Visual or Group. This will correspond to a number listed at the end of the Bookmark.
Record Power BI Bookmarks on Groups
Using groups has huge benefits for Power BI bookmarks. If you record bookmarks on groups instead of individual visuals. Now, any edits made on the content of the groups will flow through, without the need to re-record bookmarks.
For example, let’s say I want a bookmark that switches a visual from a table to a map.
First, I’m going to make the groups. Open the selection pane. Whilst holding control, click on each visual that should be in the group. Right-Click on one of the selected visuals, then click the list option named Group then in the sub menu Group. You should also rename the group, so you know what it contains.
Note that to set up a group, you need at least two visuals. In my example, I have a graph visual and a title. If you have just one, you can still set up a group. Simply add a blank text box or shape and group it with your visual. You can then delete the blank text box or shape and the group will persist.
HINT: Elements can be difficult to move or select after grouping. If you want to modify a visual, use the selection pane to select it easily. If you want to move it, click and drag the ellipsis to move it.
Add Placeholder Groups to Easily add More Visuals
Next, I will set up the remaining groups. Aside from the map group, I’m also going to add some placeholder groups. To do this, I will add a blank visual and a blank text box.
Next, I’m going to select the new group. Then I’m going to copy and paste using Control-C and Control-V to create three placeholders.
Now that all the groups are set up, it is time to record the bookmarks! For each group follow these steps:
Using control, select all visual groups including the placeholders.
Using the eye, hide all visual groups except the Map – Visual Switch.
Rename the bookmark in the bookmark pane by double clicking it.
Click the ellipsis to open the bookmark settings.
Deselect Data and Current Page. Change to Selected Visuals. The settings should look like the picture above.
Rename and Group Bookmarks
Two more Power BI Bookmarks Tips are to rename the bookmarks and to group them. In addition to renaming your visuals when adding them, you should also rename your bookmarks. For the bookmarks themselves, I do action name – function. Secondly, you should group similar bookmarks together. For example, the bookmarks in the visual switch should be put in the same group. This group can also be renamed. I often like to include the page name in the group and then its function.
Example of Power BI bookmark groups and names
Pro Tip: You will want to provide a connection between the bookmarks and the visual elements on the page. By adding an ID at the end of the Visual or Group and the Bookmark you can create a traceable link. This is especially important when you have multiple repot developers working on the same report. Adding an ID signals to the next report developer that these bookmarks are influencing the associated items on the Selection pane.
Layers
For this section, you should be familiar with the selection pane. Remember that objects at the top of the selection pane are in front of those below it.
Now that we have our groups, it makes it simpler to have buttons. In my example, I will create a button that says Graph, plus a button that says Map. If we are switching visuals, it is useful to have highlighted what visual is presented. I will highlight with a blue background and bold text.
One way of doing this, is to layer text boxes behind the buttons. First, create the buttons at the that will contain the bookmark action. This button will be see-through and slightly larger than the text boxes.
Next, create the middle text boxes that formatted for the selected button name.
Finally, create the back text boxes that are formatted for the unselected button name.
Example of the button elements
Keep it Tidy
Once we have repeated for all buttons, we can tidy it up. First, place all button elements in the same position on the page. You can do this easily using the align function.
Then, place the middle text box inside the group it relates to. This means when the bookmark is applied, the selected format will be visible for the correct visual. The trick is that we will layer the elements, so this text box will appear in front of the unselected text box. The buttons will always be on top, so the functionality will always remain the same.
Group the front buttons together, and make sure they are in front of the text boxes. These control the functionality of the Power BI Bookmarks.
Group the back text boxes together, and make sure they are at the back.
The layer sets: Visual Switch Buttons: These are the buttons that apply the bookmarks. This is the top layer and always visible. This contains the Front Buttons.
Visual Switch Groups: This is the groups we set up earlier. This contains the text box that shows the highlighted button name. As the text boxes are part of the groups, only the selected one is visible. The is the middle layer. This contains the middle text boxes.
Button Text Boxes: This contains the text boxes that go at the back and show the unselected value. This contains the Back Text Boxes.
Final Words
Out of all the Power BI Bookmarks tips, the one I would stress the most is: use the selected visuals setting. It will make your reports much easier to maintain!
If you are not convinced to use bookmark groups, it was recently announced at MBAS 2021 some extra functionality. This will allow users to apply whole bookmark groups to a page, automatically adding buttons when you add extra bookmarks.
I also described a similar tip using buttons almost two years ago. This uses slightly more advanced technique, and some functionality was not out then that is now. However, it is still valid and a viable option.
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:
Using Power Apps Power BI and Power Automate together can unleash huge benefits and enhance your solutions. Creating an end-user-centric dashboard and power app creates a simplified user experience. We will understand who will be visiting the reporting solution, the value they will receive from it, and take action through the actions below.
Use Cases for Power Apps, Power BI and Power Automate Integration
There are countless opportunities to unify these tools and use them together. Here are a few common use cases that can have huge benefits.
Editing source data directly
One of the biggest advantages of using Power apps, Power BI and Power Automate together is the ability write back data to the source system. Power BI is natively a read-only tool, allowing users to see their data. Power Apps and Power Automate provide a great way to be able to modify this data directly. A good example is sales staff looking at forecasts. They may want to make some small adjustments. For example, they know a customer has gone out of business. With the embedded solutions, they can update and write back to the source system from within the Power BI Report.
Adding additional information
Like editing the data directly, the solutions also allow for additional data to be stored. This may include adding comments to another database. A solution like this would let others see the comments when they view the reports. Or we could send an email regarding some information you have filtered in the reports.
Take Action on data
As well as adding and storing information, several actions can be taken through Power apps, Power BI and Power Automate integration. Maybe in a sales dashboard, we want to alert someone about a trend. Alternatively, we could kick off a campaign by reacting to information in a marketing dashboard. There are huge amounts of actions that can be taken in Power Automate. Power Apps provides a great shell to control and kick off these actions. You can even call Power Automate to keep track of Power Query errors.
See our Power BI data elsewhere
It is not limited to just surfacing Power Apps in the Power BI report. We can also view our Power BI data in our Power Apps. Thus, allowing business users to access the information when using the apps. There is also the possibility to set off Power Automate actions based on data-driven alerts.
Create a Unified Power Apps, Power BI Experience
An example of a Power App embedded in a Power BI Report
Here is a quick tip before you start to plan how to build your Power Apps, Power BI and Power Automate integration. Write down two things and keep this at the center of every action taken.
Understand who your audience is by writing down the end-user. Write organizational titles and personalities (technical ability). Writing these down and seeing these as a reminder throughout the build will help guide visuals, verbiage, and process flow.
Now that we understand the audience, write down the goal of surfacing the data and how it affects the audience.
Suppose the tool doesn’t have a defined audience and purpose. It’s like having a destination but no map, a vehicle, and an idea but no vision to truly connect the dots. Ensure that these are clearly stated, placed somewhere visible even on the Power App or within the dashboard while testing to keep these critical pieces of information at the forefront while combining the end-user experience from two separate tools to one unified solution.
Interact with your data in real-time
The questions above drive cohesiveness and solution unification, but how are we informing the audience, and for what purpose? The purpose of integrating a Power Apps, Power BI report is to enable real-time Action. In fact, it’s at the most critical point while the consumer is digesting the information.
Build a power app to drive input through Power BI’s self-service capabilities. Power BI users visit reports to visualize meaningful data, discover trends, and ultimately make decisions. Allow Power Apps to complement the users by enabling the end-user to take Action while the end-user has data in front of them. Combing Power BI, Power Apps and Power Automate reduces context switching. It provides a simple way for the end-user to make meaningful areas of interest actionable. Keep these tools as close as you can to maximize decision-making capabilities and employee productivity.
Gain Feedback
Build a power app to drive actionable outcomes through process automation. Viewing insights is essential. However, taking Action on these insights is equally as important. Embedding a Power App on the form and surfacing the data through Power Apps will bring that information to the end-user in a way that Power BI can’t, providing data modifying capabilities. Whether it’s one column or a large number of columns be edited, Power Apps can filter based on what you have selected. It brings in the form so that it only pulls in the relevant information. Keep this in mind while Sales team members are looking at their forecast and are 5 minutes before their weekly quota call and making some changes. They modify their data to see it very easy to use and don’t require them to context switch, losing their train of thought.
Learn How
Watch the video below to learn how to use Power Apps, Power BI and Power Automate together!
This was originally recorded from the Milwaukee Brew City Power Platform User Group on August 24th, 2020.
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:
I was having a candid conversation with Phil Seamark from DAX.tips about Aggregation Tables. During that conversation, I was asking about patterns in using Aggregation tables. Within that 10 minute conversation I was blow away by all the possible patterns. Because of this, we pleaded for Phil to present these patterns to the Milwaukee Brew City User Group.
While these patterns are described in detail here are the various patterns that can be used for Aggregation tables. Also, Phil includes a great introduction, found here. For each of these articles Phil describes proper usage for the pattern.
The Filtered Aggs would contain multiple Aggregation tables of the same data. But, each Agg table could contain different gains of data. For Example, data aggregated by Week, Month or Year.
Finally, the Incremental Aggs. This type of aggregation would be used when aggregating transactions per day by store, and or product.
Thanks Phil
A special thanks to Phil for presenting. Since, we know you are a busy guy doing tons of great work. Thank you for taking time out of your day to present this wonderful topic. We hope you enjoy this exploration into Agg Tables.
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:
