Power BI, as part of the Microsoft Power Platform, is complementary to its sister application of Power Apps (for no-code/low-code application development), Power Automate (for workflow development), and Power Virtual Agents (for chatbots.). Each of these applications works well with one another. Strong integration opportunities also exist between Microsoft 365 (Word, Excel, PowerPoint, and SharePoint) and Dynamics 365.
When you’re looking to realize the value of your data using Microsoft applications, or even third-party applications, Power BI can provide the insights you and your organization look for at speed and scale.
Getting to know the Power BI versions
When you want to select the version of Power BI that’s right for you, it might become a bit perplexing. That’s because several versions of Power BI are available — some free, some not so free.
When referring to free versus paid, you should always associate this with Power BI Services, the online platform for Power BI. Power BI Desktop is always free. Let’s start with the licensing models:
| Version | Description |
| Power BI Services Free | A user can access content in My Workspace. |
| Power BI Services Pro | A user can publish content to other workspaces, share dashboards, and subscribe to dashboards and reports. Users can share only with users who have a Pro or Premium license. |
| Power BI Services Premium Pay-per-User | A user can publish content to other workspaces, share dashboards, and subscribe to dashboards and reports. Users can share with users who have a Pro or Premium license. The main difference here is the number of refreshes and capacity allotted at the user level. This option is intended for individual users with big data-oriented sets. |
| Power Bi Services Premium Pay-per-Capacity | It has the same features as Pay-per-User, except that the scale and size of the storage repository and refresh rate are even higher than the pay-per-user model. |
It may get a little confusing because some of the products advertised are free and others require licensing. The versions of Power BI where production-ready data can be exposed to users include Free, Pro, Premium, Mobile, Embedded, and Report Server. Here’s a description of each one:
| Product Version | Description |
| Desktop | The free desktop version of Power BI allows a user to author reports and data analytics inputs without publishing them to the Internet. If you want to collaborate or share Desktop output, you need the Pro or Premium version. |
| Free | Considered the entry-level free Cloud version, it lets you author and store reports online versus the desktop. The only drawback is its limited storage capacity and no opportunities for collaboration. |
| Pro | In the entry-level paid version of Power BI, you get a larger storage allocation and the ability to collaborate with Pro licensed users. |
| Premium | The enterprise paid version comes in two editions: per user and capacity. Per-user licensing is intended for those with big data aspirations who also need massive storage scale without the global distribution requirements. Capacity is useful for an enterprise that intends to have many users. There is one catch with Capacity licensing: You also need to obtain Pro licenses. What you’re paying for is the storage and security — which is the killer feature. |
| Mobile | Intended to be a complementary product to manage reports, dashboards, and KPIs on the go, it offers limited, if any, authoring capabilities. Your ability to collaborate on mobile varies depending on your license authorization. |
| Embedded | This is a way to integrate real-time reports on public- or private-facing products using the Power BI API service in Microsoft Azure. |
| Report Server | This server-based Power BI product is intended to produce reporting output offline. Users store their reports on a server, not online. |
Power BI Desktop and Power BI Free cost nothing, but you have little to no opportunities for collaboration. If you want to collaborate, you need to purchase at least a Power BI Pro license.
The basics of how to import and transform data in Power BI
Whether you’re using Power BI Desktop or Power BI Services, you want to know the various ways to import data so that you can produce visualizations, reports, dashboards, and KPIs after the modeling and transformation activities are complete.
You can use one of four storage modes in Power BI:
- Direct Import: You can import the data locally, which allows for data caching. When you ingest the model, a user can employ all Desktop features available with Power BI.
- DirectQuery: You can create a connection to the data source. In this mode, the data isn’t cached. Instead, the source must be queried each time a data call is made. Most data sources support DirectQuery. When you’re looking for a choice for big data, consider DirectQuery. However, if you require flexibility, steer clear.
- Live Connection: You can ingest data from the SQL Server Analysis Services in connection with Power BI Desktop or Power BI Services. Live Connection supports calculation-based activities that occur within a data model.
- Composite Models: When you need to combine the best of Direct Import and DirectQuery or fulfill the requirement to connect to several DirectQuery connections, the composite model is one to consider.
Regardless of the method you choose, the import process starts in Power BI Desktop when you go to the Home tab. You’ll find various ways to ingest data. The bulk of your data sources can be found using the Get Data contextual menu in the Data area of the Ribbon’s Home tab, shown below.
If you want to avoid using Power BI Desktop altogether and import an existing data set you have or perhaps even create one online, you can carry out this task in one of three ways — Power BI gives you that option.
Start by clicking the Create icon (the plus sign [+] on the navigation bar along the left side; see the following figure), and then either create a new dataset online (using the Paste Manually Enter Data option) or publish an existing dataset to Power BI Services (using the Pick a Published Dataset option.)
Learning your visualization options in Power BI
You’ve gone ahead and transformed and modeled the data in Power BI. Perhaps you didn’t use Power BI to complete the data cleansing and transformation process. Still, you have a large dataset that you want to visualize. Your endgame is to bring any sort of dataset into Power BI Services to create reports, dashboards, and perhaps a few KPIs.
You can create reports and KPI’s using Power BI Desktop and then publish them to a Power BI workspace. Alternatively, you can produce visualizations directly from your dataset in Power BI Services. Either way, your end goal is to produce a type of report that’s shareable (assuming that it’s more than just you accessing the report).
In any of these scenarios, you start with accessing a report. In Power BI Desktop, visualizing your data all happens on the Reports tab. (See the following figure.)
If you go the Power BI Services route rather than work with Desktop, you’d create a new report in a workspace.
Assuming that you have associated a dataset to a report, you have fields in the Fields pane, visualization options in the Visualizations pane, and the ability to craft your report in the Filters pane.
You can filter fields (as shown for the term Recipients) when the dataset has hundreds of fields. You tighten your queries in the Filters pane.
Power BI includes more than 20 out-of-the-box visualization options. Many more can be downloaded from the Microsoft website. The visualization options run the gamut from bar charts, pie charts, treemaps, tables, KPIs, and matrixes.
Know the nuts and bolts of DAX for Power BI
You can accomplish 95 percent of your work using the low-code, no-code capabilities integrated into one of the versions of Power BI. At times, however, you may require a granular manipulation of data or a deeper dive into your data.
When you want to make sophisticated calculations with little to no effort using a structured approach, turn to the syntax language Data Analysis Expressions (DAX).
DAX consists of formulas and expressions used for data manipulation in data analysis tools such as Power BI. Functions, formulas, constants, and operators are used as part of DAX to create expressions that are easily entered using the editor tools in Power Query, which is the data transformation utility within the Power BI suite. (See the following figure.)
If you’ve ever used Microsoft Excel formulas and calculations, you soon notice that DAX is merely an advanced version with sophisticated data manipulation capabilities targeted at business intelligence and data modeling tools.
DAX combines three fundamental concepts: syntax, context, and functions. Each time you use the Power Query editor, you need to apply specific rules to create formulaic expressions to create more precise calculations or manipulate datasets. So, what are syntax, context, and functions exactly?
- Syntax refers to the components within the formula you make. It’s the language used in the formula, such as the command, sign, operators, column or row, or tables. In other words, syntax is the programmatic structure.
- Context refers to the target row incorporated into the formula for retrieval or calculation. You need to know two types to be literate in Power BI DAX: row and filter context.
- Functions refer to the predefined and known commands in a system. These are the commands that are readily used to manipulate data without having to craft extended coding samples. More than 250 functions are available for DAX in Power BI, which is well beyond what other Microsoft solutions offer.
Be consistent with your naming conventions and data formatting. Otherwise, it can become overly complex to update your DAX formulas.
Look for these Power BI service-only features
Power BI Desktop is useful when you want to work through datasets on your own. The second that you want to share and collaborate with others, you need to begin publishing your work from the Desktop to Power BI Services, the online platform for Power BI.
The main reason you use the online version is to allow others to view your deliverables. That can be accomplished using the Power BI Services Free version. When you decide, though, to allow users to share and collaborate in Power BI, you need to leverage the workspace.
Power BI has two types of workspaces: a project workspace and a personal workspace. The Power BI workspace contains all content specific to an app. When designers create an app, they bundle all the content assets necessary for use and deployment. The content might include anything from datasets, dashboards, and reports.
Whereas the project workspace is intended for sharing and collaboration using a collaboration scheme with others, My Workspace is similar to Power BI Desktop. The only difference is that you control your self-created assets in My Workspace. In contrast, project workspaces can also be managed by others.
You need to have the correct type of license to access another user’s workspace for collaboration and sharing.
The workspace isn’t the only feature that makes Power BI Services worth the investment. You get prebuilt reporting capabilities, including several that leverage Microsoft’s impressive artificial intelligence infrastructure:
- Access management: Control who has access to which reports, dashboards, and datasets within a workspace.
- Create mobile-ready Power BI apps: Packages content specific to your project in an app. Content may include reports, dashboards, and datasets.
- Quick Insights: A cloud-only Power BI solution helps you analyze datasets and find patterns, trends, and outliers.
- Analyze in Excel: If your data is a bit too complex to deal with in Power BI and you want to review a smaller subset with a familiar business productivity tool, Power BI integrates with Excel to allow an analyst to view and interact with their data using PivotTables, charts, and slicers.
- Usage metrics reports: Treat this one as a status check on how your content is viewed or shared across platforms.
- Paginated reports: A built-in, online-only option that allows users to create print-friendly reports for sharing and distribution. These reports are static because they’re fixed for print-ready presentation.
- Data lineage: Want to know what data sources are used for what reports and datasets? If you want an upstream and downstream review of your data, data lineage offers a 360-degree view of your data chronology.