70+ Power BI Interview Questions and Answers

[ad_1]

power bi interview questions

Looking to pursue a career in Power BI? We have compiled a list of important Power BI Interview Questions for you to prepare. But before that, let’s brush up on the basics of Power BI.

Power BI is a Business intelligence tool or a Business analytics solution developed by Microsoft. It unifies data from multiple sources to create interactive reports and dashboards that provide actionable insights and drive business results.​ It provides us with three ways to consume & build our reports – Power BI desktop, Power BI Service, and Power BI Mobile.

Power BI Desktop: It can be called a local setup for Power BI as it is installed locally on our machines. This is the application that we use to create our reports and dashboards. It includes datasets, visualizations, tiles, and reports.

Power BI Service: It is a web component of Power BI. It is cloud-based. It utilizes Software as a service. It is used for the collaboration of reports/dashboards, basically sharing them with end-users. It includes Datasets, visualizations, Tiles, reports, Dashboards, and Apps. Dashboards and Apps are additional features in Service and are not available on desktop.

Power BI Mobile: It is used to view reports and dashboards- the ones you have created or the ones shared with you. It allows you to securely access and view live Power Bi reports/dashboards on any device with the Power BI app installed for Windows, iOS, or Android. It also provides an option of natural language query to ask questions from your data.

Power BI Access/License

  • Power BI Pro: It is an individual user license. It allows users to share reports with others and lets users read and interact with reports/dashboards shared with them. Users with a license can collaborate with other Power BI Pro users. It also allows creating App Workspaces.
  • Power BI Premium: It is a group license for App Workspaces. It is called a capacity-based offering. It provides flexibility to publish reports broadly across an enterprise or to a distribution list. The important thing is it does not require recipients to be licensed individually per user.

Let’s look at a few useful Power BI Interview questions. We have categorized them as beginner, intermediate and advanced depending on how you learn them by their usage. We hope you find them informative.

  1. Beginners
  2. Intermediate
  3. Experienced

Power BI Interview Questions:

Beginner Level Questions

What is Power BI?

Power BI is a Self-Service Business Intelligence tool powered by Microsoft. It was launched in 2013. Today, it has gone way ahead of its competitors like Tableau, QlikView & traditional Bi tools, and other competitors.

You can connect to various data sources from different databases like SQL, Oracle, etc. using Power BI, build relationships using Data Modelling & transform the data in Power Query Editor before actually loading it to Power BI for visuals. DAX Formula and calculations help in data analysis, and they can create interactive reports & dashboards to share with the people.

How would you define Power BI as an effective solution?

“Power BI is a business analytics tool,” according to Microsoft, “that enables you to display your data and share insights across your organization, or embed them in your app or website.” The solution consists of multiple goods and services, and the value and diversity come from combining the separate pieces and exploiting how they interact. Some of the benefits of Power BI as an effective solution are;

  • Connect to information
  • Data transformation and modeling
  • Make graphs and charts.
  • Create visual groupings for reports and dashboards.
  • Use the Power BI service to share reports with others.

What is a dashboard?

A Power BI dashboard is a single page, also known as a canvas, that uses visuals to convey a narrative. A well-designed dashboard offers only the highlights of the tale since it is confined to one page. For further information, readers may look at associated reports.

What are the various Power BI versions?

Power BI Premium capacity-based license, for example, allows users with a free license to act on content in workspaces with Premium capacity. A user with a free license can only use the Power BI service to connect to data and produce reports and dashboards in My Workspace outside of Premium capacity. They are unable to exchange material or publish it in other workspaces. To process material, a Power BI license with a free or Pro per-user license only uses a shared and restricted capacity. Users with a Power BI Pro license can only work with other Power BI Pro users if the material is stored in that shared capacity. They may consume user-generated information, post material to app workspaces, share dashboards, and subscribe to dashboards and reports. Pro users can share material with users who don’t have a Power BI Pro subscription while workspaces are at Premium capacity.

What is visualization in Power BI?

Visualizations are used to represent our data in more than a tabular form. In Power BI, we have the design space where all the elements of a report, different visuals, slicers/filters, etc. These elements, when put into the design space, are called tiles. All the visuals that are available in Power BI are put under the Visualization Pane. It includes basic visuals such as bar, pie, line, column, and map with their varieties. Along with them, we do not have very common visuals like waterfalls, funnels, gauges, etc. We have a lot of custom visuals available as well. They can be imported into the report from App Marketplace. And then we can bring them into our design view.

If one knows R or Python, then we also have the option to create visuals using code. 

What are the types of visualizations in Power BI?

The most common types of visualizations in Power BI include; 

  • Area charts
  • Bar and column charts
  • Combo charts
  • Decomposition tree
  • Doughnut charts
  • Funnel charts
  • Gauge charts
  • Key influencers chart

What do we understand about Power BI services?

Power BI is a set of software services, applications, and connections that work together to help you produce, distribute, and consume business insights in the most efficient way possible for you and your company. The SaaS (Software as a Service) component of Power BI is the Microsoft Power BI service, sometimes known as Power BI online. Dashboards in the Power BI service allow you to keep an eye on the pulse of your business. Dashboards show tiles that you may click to launch reports for additional investigation. Dashboards and reports are linked to databases, which collect all essential data in one location.

What is the calculated column in Power BI?

Calculated columns are built to extend the data attributes. They are those columns that are created when the available columns in the data do not serve our purpose or we are not able to generate any useful insight from the same. That is when calculated columns come into the picture. And these are created using different DAX functions as per need. We can have a simple example of a date where we do not want to work with the complete date but individual day, month, or year. For this, in the data part of the Power BI desktop, we have the option of a ‘New Column.’ When we click that, we get a formula bar on top (like excel), where it asks us to type the new column name with its calculation. So, if we consider the data example, it will be something like this: yearcol = YEAR([Date column]) where YEAR is a DAX function. And ‘yearcol’ is your calculated column.

What is a legend in Power BI?

Legends are part of Power BI visuals. They represent categories in a visual and are usually color-coded. In some visuals, we can add a category dimension as legend explicitly. One example can be in stacked bar/column charts, where the stacks represent different categories, and these categories are color-coded. These categories are represented in the legend. And every visual where the legend is applicable has a separate formatting section where we can specify its font and font size, whether we need it to be visible or not, or even its position(top, bottom, left, right)

What is drill down in Power BI?

Drill down, as the name suggests, is going downwards. And in Power BI, it is going down a hierarchical category or dimension. Basically, when we have data attributes like a date where we have a default hierarchy of year->quarter->month->day, then when using the same field in a visual, in Power  BI, it by default takes the hierarchy form of the field. This gives the option of a drill-down feature denoted by up and down arrows. Also, we can right-click on the element in visual and get the option of drill-down.

How to calculate the average in Power BI?

Average can be calculated in two ways- 

one is when we add a measure to a visual; by default, it summarizes any measure. When we click on the drop-down for the measure- we can change from Sum to Average. This gives us an average.

The second one is creating a calculated measure for average using the AVERAGE() DAX function.

What is a Power BI desktop?

Power BI Desktop is a free tool that enables you to connect to, transform, and visualize your data on your local computer. With Power BI Desktop, you may connect to a variety of data sources and integrate them into a data model (a process known as modeling). This data format allows you to create visuals and collections of visuals that you can share as reports with others in your company. The majority of customers who work on business intelligence projects utilize Power BI Desktop to develop reports and subsequently share them with others via the Power BI service.

The following are the most typical uses for Power BI Desktop:

  • Connect to information
  • To construct a data model, transform and clean the data.
  • Create graphics that give visual representations of the data, such as charts or graphs.
  • On one or more report pages, create reports that are collections of graphics.
  • Use the Power BI service to share reports with others.

What are the available views in Power BI?

Three perspectives are available in Power BI Desktop:

Report View: The first option is the Report view, which is at the top of the list. It’s also the default view, so it’ll be the first thing you see when you launch Power BI Desktop. This view is used by creators to produce visual reports based on their data. This is the screen on which many creators spend the most time. While in this view, you can also add many pages to a single report, so that if you ever distribute these models via Power BI Service, you can have all of your associated data in one place.

Data View: The option in the center is the Data view. You may see your data in the Data view once you’ve taken the time to model it. Data view sets it out piece by piece in a grid structure, rather than displaying your images like the ones you’ve developed. The Data view can assist you in gaining additional insights into your data so that you can evaluate it from various perspectives. This way, you can make the most of your data understanding and make the best judgments possible based on it.

Relationship View: The Relationship view, also known as the Model view, allows designers to examine the connections between their data models, as you might have guessed from the name. On a single page, you may examine all of the models you’ve developed for your report. You can also generate charts based on only particular subsets of models while you’re there. As a consequence, you may discuss as many different datasets as you want without having to look at each one individually.

How to update the Power BI desktop?

Power BI is a very active community. And in terms of features Power BI team is open to feedback for its new features and new features to be added, if any. And Power BI keeps improving by adding new features in terms of visualizations, data connectors, etc., monthly. And when the updates are available in the new version, there are multiple ways to update the desktop to get the new features. 

  • First is if you have installed the Power BI desktop via Windows or Microsoft store, then it is updated automatically once a new version is available.
  • Second, if you have installed the application from the web, then we will have to update it manually. In this case, if in options and settings of the application-> Under Updates-> provide notification when an update is available is selected-> then in the bottom right corner in Power BI desktop it tells that new version available(click to download) whenever a new version has come up. Then either you click on the option or go directly to the web to download and install the latest version.

What is Power BI Cloud?

Power BI Cloud is a Cloud-Based Data Visualization set-up that gives access to its users for editing, viewing, creating, and sharing the dashboards’ reports & datasets across organizations and a group of people by protecting the Admin Rights & Credentials. Access is required to view & edit these reports & dashboards.

How to change Power BI Password?

It is a very easy & quick process to change your Power BI Service Password. Follow this link and the process they have mentioned.

How to Connect to Oracle Databases in Power BI?

To connect to Oracle Databases from a server, you need to open the Power BI desktop in your Machine & click on the get data, under the Home Tab in Ribbon. Once you do so, you will get a list of Files/ Databases & other Sources. To connect to the Oracle databases, click on database & then select Oracle database & click on connect.

Once you do so, you will be asked to give the Server Information that you want to access. Provide those details, select the connectivity Mode-Direct Query or Import Mode, whichever you prefer, based on your requirement & then Click OK.

Once you click on OK, a window will be opened asking your credentials to access the database. By using your credentials (Username/Password), you will then be allowed to access the database, select the Schema/Drag & drop the tables, etc., in the preview section & click to load to view the required tables in the Power BI environment.

What is Power BI in Office-365?

Power BI is a cloud-based business analytics tool & Intelligence tool that allows its users to connect to, analyze & visualize data with a faster speed. It Basically connects its users to a broad range of live data through its dashboards & interactive reports and delivers insights & visualizations that help organizations make better decisions.

Power BI is a part of the Office 365 Enterprise E5 version. To learn more about the same, you can visit Power BI.

Power BI for Office 365 & Power BI is two different products. Power BI for Office 365 is Deprecated 1st version Service.

What are the components of Power BI?

There are 6 main components of Power BI:

Power Query, Power Pivot, Power View, Power Map, Power Bi Desktop, Power Q & A.

Let’s discuss one by one in detail: 

Power Query

Power Query is nothing but the data transformation and preparation facility provided by POWER BI. It is a self-service ETL tool, which runs as an Excel add-in. 

It helps its users with the capabilities to pull out the data from various sources, like Excel, CSV, XML, Oracle, SQL, etc., and get connected to it to combine, manipulate and refine the data so as to meet your analysis requirement.

It is provided as a part of the Power BI Desktop & can be used to transform the data.

Power Pivot

Power Pivot is an in memory Data Component, that provides highly compressed data storage and is used for data modeling. It is a technique that uses Data Analysis Expression (DAX) language for Data modeling.

It also gives you the capabilities to create the required data models, establish relationships between the tables, and perform various calculations. 

The below image represents the Model Pane under which the relationships are maintained among the three tables. If the tables are not connected to each other, the accurate data will not get reflected in the charts for the tables which are connected to each other and have relevant data stored in different tables.

Power View

The Power View technique in Power BI helps us to create different kinds of visuals, like interactive graphs, maps, charts, etc., which bring life to the data. It can easily get connected to various data sources and filter out the data for every visualization or even the entire report. Pictorial representation of Ribbon chart, bar charts are as shown below for reference.

Power Map

Power Map in Power BI is a 3-D data visualization tool offered by Microsoft to help you create maps out of your data & plot millions of rows of geographic & temporal data visually in 3-D format. Power BI gives you two types of Visualization, Bubble & Shape Maps. It works with Bing maps to give you the best possible visuals based on given information on the latitude, longitude, address, country, state, city, etc.

Power BI Desktop

Power BI Desktop is a development environment for working with the Power Query, Power Pivot, and Power View.

It is used to work on the data & make visuals & reports to publish on the Power BI service & later can view on the Power BI Mobile. Here you get every solution under one roof for easier BI development and data analysis experience.

Power Q&A

The Q&A feature in Power BI lets you explore your data using intuitive, natural language capabilities in your own words & ask questions about your data. Quickest possible way to get an answer to the question asked about your data is using natural language.

Q & A Basically recognizes the words you type & figures out where to find the answer from. For example, You can ask your data like what was the total profit last year? You can use this feature once your Data Modelling is done & reports are built & you have deployed it into the Power BI service. In the below image, Q & A feature is shown, i.e., you can ask questions about your data. 

Suppose you ask total sales in 2015, you will get the result as shown in the below image.

Below image reflects how to use Q & A in Power BI Desktop, so quick & easy to do. 

What are the major uses of Power BI?

Power BI is an excellent Business Intelligence Tool that helps developers to create, visualize, report, interactive dashboards & share with the end-users depicting insights about the business to make better business decisions.

There are many things that one needs to learn in Power BI.

Components of PowerBI, Power BI Desktop, Workflow in Power BI.

The Three Main Panes in Power BI(UX): 

  1. Data Preparation: If we want to analyze the data in Power BI, we need to first look at the data as if the data is model ready, free from errors, and missing values if there are additional columns in the data which are not required for the particular analysis, before loading the data into Power BI. This is done using Power Query Editor.
  2. Data Analysis: Earlier, it was known as Power Pivot. Data Analysis is done using DAX calculations wherein we can perform additional calculations, and add/remove columns. Various operators & functions are available to carry out these operations.
  3. Data Visualization: This is the final stage wherein we can create reports & visuals using various types of charts, filters, slicers, cards & graphs available in Power BI. Data Modelling & DAX are very important to be learned by any individual to be perfect in Power BI, because they help in bringing out many important things such as connecting to various databases like SQL Server & other sources.
  4. Power BI transformations: We need to have a good understanding of the various transformations to perform while making the data ready for visuals.

Bins, Change the Data type of a Column, Hierarchies, Joins, Pivot Table, Query Groups, Reorder or Remove Columns, Rename Column Names, Rename Table Names, Split Columns, Pivot Table, Calculated Fields.

Combine Multiple Tables, Clusters, enter data or Copy & Paste data from Clipboard, Format Dates, Groups, 

Power BI various Charts & Maps, Power BI Filters, Creating Power BI Reports, Dashboard, Workspace, Publishing Reports to Power BI service. Sharing Reports, Datasets, Dashboards, Power BI Q&A.

What is Power BI-Embedded?

Power BI Embedded is an analytics solution, provided as a Microsoft Azure Service, a platform-as-a-service (PaaS), wherein the developers & ISVs (Individual Software Vendors) can easily embed their dashboards, Visuals & reports into an application for their customers.

Fully interactive reports & visuals can be embedded into the applications.

The great thing about Power BI Embedded is that your customers are not required to have knowledge about Power BI. Power BI gives you the capability to create an embedded application using two different methods.

The first method is using a Power BI Pro account & the second method is using the service principal.

The Power BI Pro account acts as the master account of your application. This account will help you generate the required embed tokens for providing access to your customers to view the shared Power BI dashboards and reports.

The service principal embeds Power BI Dashboards/Reports/Visuals into an application using an app-only token. Now this will allow you to generate the embed tokens for providing access to your shared application’s Power BI dashboards and reports.

What are the various refresh options available in Power BI?

There are essentially four major types of refresh options available in Power BI

  • Package refresh
  • Model/data refresh
  • Tile Refresh
  • Visual container refresh

What are the different connectivity modes in Power BI?

  • Import (data storage in Power BI)
  • DirectQuery (data storage outside power BI)
  • LiveConnection (data storage outside power BI)
  • Composite

Where is the data stored in Power BI?

The nation or area for the business identity is chosen by the first user in your organization who signs up for Power BI or Microsoft 365. The cloud’s shared identity and access management service, Azure Active Directory (AAD), establishes a tenant in the data center region nearest to the chosen nation or area. AAD is a multi-tenant service, with each enterprise represented in the data center as a separate tenant. The data is saved in the area you choose at sign-up. This region will be the same for all users in your organization, regardless of their location. The chosen region should, ideally, be in the same geographical area as the majority of your consumers.

Power BI can connect to which data sources?

Power BI can be connected to a variety of different data sources, some of which are as follows;

Excel, Jason, SQL server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata, SAP HANA, Microsoft Azure SQL database, MS Azure marketplace, MS Azure HD insight, blob storage, Hadoop file, Sharepoint list.

How to add a Calculated Column in Power BI?

A calculated column is an extension of a table that’s evaluated for each row. Calculated columns are a part of the data & they are stored in the x- Velocity in-memory storage of Power BI; similarly, all the other data is imported from a data source. By definition & nature, there is only one difference between a calculated column & the normal (non-calculated) column, i.e, the values of calculated columns are calculated using the DAX formulas/functions and the values from the other columns.

Example: Suppose you have two columns: Product Price & Product Profit. You want to calculate the cost & add it in a new calculated column.

So, you have to select the new column & write the Dax formula in the Formula Bar Unit Cost = Product Price-Product Profit.

Go to Table tools & find the new Column tab, click on that, and you will be directed to the new Column added:

To add a calculated column in Power BI, we can achieve it in different ways:

Method-1: Go to the Modeling tab, table tools, select a new column, & add the DAX formula, give a name to the column.

Method-2: By Selecting the table in which the particular column is supposed to be added, from the fields pane, right-click & select a new Column, use the DAX Calculation/Formula & give a name to the new Calculated Column.

Method-3: You can create a calculated column in the Power Query Editor by right-clicking on the particular table and selecting the new column, while in the transformation phase itself.

What are the types of relationships in Power BI?

There are basically 4 types of relationships in any database design. Out of which, three types of relationships are supported by Power BI.

One to One Relationship: In this type of relationship, both side tables can have unique values. This relationship is not quite common.

One to Many:  In one to many cardinalities, one side of the table will have unique values but the other(many) sides of the table can have multiple values in it. For example, Product table & Customer Table, you can connect /join them with the Product ID. The customer table can have multiple customer IDs for the same Product ID. When you relate them from the product table to the customer table, it will be one-to-many relationships.

You can see the status of the relationships in the Power BI Desktop’s Model view, by just looking at the indicators as (1 or *), on either side.

Many to One Relationship: In this type of relationship, one side of the common column will have multiple values & on the other side table it will have a unique value. For Example, in the below-shown images, there are two Tables Fact table sales & dimension table dates. The date can have only the Unique values but the sales table, order date, and key column can have multiple values for a particular date.

Cardinality is not supported by Power BI.

In Power BI, we can have many relationships in between the tables, but only one relationship is active at a time.

Further, we also see the relationship status of a particular relation in the Power BI model view, active relation is a straight line, whereas, an inactive relation will be a dotted Line. For further understanding, please refer to the images shown below:

Intermediate Level Questions

What is KPI in Power BI?

KPI stands for Key Performance Indicators. These are specific measures or calculations which provide us with insights into our data and help us make decisions based on them. These are the parameters on which business decisions are made. Power BI has a visual called KPI, which helps to visualize these specific measures. This KPI visual shows the trend of the specific measure/calculation across time(year/month) and shows how it has changed. It helps in tracking the change%.

What are filters in Power BI?

Automatic filters: When you design a visual, automated filters are the filters that are automatically added to the visual level of the filter pane. The fields that make up your visual are used to create these filters. This filter in the pane can be edited, cleared, hidden, locked, renamed, or sorted by users who have edit rights on the report. Because the visual relates to certain fields, they can’t erase automated filters.

Manual filters: When editing a report, manual filters are the filters that you drag and drop into any portion of the filter pane. You may modify, remove, clear, hide, lock, rename, or sort this filter in the pane if you have edit rights on the report.

Include and exclude filters:- When you utilise the include or exclude feature for a visual, include and exclude filters are immediately added to the filter pane. You can remove, lock, hide, or sort this filter in the pane if you can modify a report. Because it’s related to the include and exclude capabilities of visuals, you can’t update, clear, or rename an include or exclude filter.

Include and exclude filters:- When you utilise the include or exclude feature for a visual, include and exclude filters are immediately added to the filter pane. You can remove, lock, hide, or sort this filter in the pane if you can modify a report. Because it’s related to the include and exclude capabilities of visuals, you can’t update, clear, or rename an include or exclude filter.

Drill-down filters:- When you utilise the drill-down feature for a visual in your report, drill-down filters are immediately added to the filter pane. You can update or clear the filter in the window if you can edit a report. Because it’s related to the drill-down feature of visuals, you can’t delete, hide, lock, rename, or sort this filter. Select the drill-up button for the graphic to remove the drill-down filter.Cross-drill filters :- When a drill-down filter is given to another visual on the report page using the cross-filter or cross-highlight feature, cross-drill filters are immediately added to the pane. Because it’s related with the drill-down feature of visuals, you can’t remove, clear, hide, lock, rename, or sort this filter, even if you can modify a report. You also can’t change this filter because it’s derived from another picture. Select the drill-up button for the visual that is passing the drill-down filter to delete it.

How to create filters in Power BI?

Filters are an integral part of Power BI reports. They are used to slice and dice the data as per the dimensions we want. Filters are created in a couple of ways.

  1. Using Slicers: A slicer is a visual under Visualization Pane. This can be added to the design view to filter our reports. When a slicer is added to the design view, it requires a field to be added to it. For example- Slicer can be added for Country fields. Then the data can be filtered based on countries.
  2. Using Filter Pane: The Power BI team has added a filter pane to the reports, which is a single space where we can add different fields as filters. And these fields can be added depending on whether you want to filter only one visual(Visual level filter), or all the visuals in the report page(Page level filters), or applicable to all the pages of the report(report level filters)

How to sort data in Power BI?

Sorting is available in multiple formats. In the data view, a common sorting option of alphabetical order is there. Apart from that, we have the option of Sort by column, where one can sort a column based on another column. The sorting option is available in visuals as well. Sort by ascending and descending option by the fields and measure present in the visual is also available.

What is the difference between MSBI and Power BI?

MSBI Power BI
MSBI is Microsoft Business Intelligence  Microsoft Power BI
It is a consolidated product that helps in doing ETL processing Power BI is mainly a Business Intelligence tool with very limited ETL processing capability
It has three components – SSIS (Integration), SSAS(analytical reports), SSRS(server-based reporting) Power BI has – A Power BI Desktop, Power BI Service, and Power BI Mobile
MSBI must be installed locally to be able to work Power BI is cloud-based. We can develop reports directly in the Power BI service i.e. in the cloud workspace. No need for a local deployment.
It has a complex interface and is difficult to learn It has a simple interface and is easy to learn as it is based on excel and Power Pivots.
Can handle only structured and semi-structured data Can handle structured, semi-structured, and unstructured data as well
Natural Language Query is not available Natural language query available
Can handle large data sets In the case of large data sets, the direct query needs to be used to build reports.

How to share Power BI dashboards?

Power BI reports/dashboards can be shared in multiple ways. If you and your end-users have a Power BI Pro license, then-

  1. We are using the share option in reports and dashboards.- This option enables access to a report or dashboard to individual users.
  2. Using content packs – Publish your report/dashboard along with the data set as a content pack and then share it with either a group or individual or open it for the entire organization.
  3. Publish your dashboards and reports into App Workspace and share the App link to a group or individual or open it for the entire organization. When publishing as App- we can select the reports and datasets that we want to be included in the App.
  4. The report can be embedded into Web or SharePoint, for which we need the embed code. This embed code is added to the website code or the SharePoint code.

Another way to share the reports is by printing or exporting the report as PPT or PDF. With these options, we can share the report, but they are not interactive.

How to publish a dashboard in Power BI?

To publish a dashboard, first, we need to be signed in to Power BI using our professional account; it only takes the work/ org account. Once you are signed in, there is a publish option available. Clicking on it opens a small window- which asks you to select the workspace into which you want to publish your report. If any workspace is there to which you have been provided access, you can choose that depending on data security, or otherwise, by default, you have the option of My Workspace, which is the default space provided for you. So, you can publish your report there.

How to add Page Level Filters in Power BI?

The Power BI Page Level Filters are useful to filter the charts (or visuals) present on the page. For example, if your page contains four charts, then you can use this page-level filter to filter those four reports at a time.

To demonstrate the Page Level Filters in Power BI, we can take an example of the Ribbon Chart that we created.

Go to the Filters section; you need to use the Page Level Filters section to create a filter at the page level.

You can drag and drop any column. For example, we have taken Category from the fields section to the “Filter on this Page “area in the Filters Pane to create Page Level Filters.

Expand the Category to view the filter types. By default, the Basic Filters option is selected as the Filter type. We can Select any Column to Filter the data for.

What is MDX in Power BI?

MDX is an Analysis Services Tabular capability that is basically used to help improve the user experience in Excel and various other applications that use MDX query language against the tabular form of the models and Power BI datasets. To have a hands-on the MDX, try it by deploying your datasets in Power BI and then connecting with Excel by using analyze in Excel, the new Power BI Datasets within Excel feature, or you can connect to this dataset on Power BI Premium as well through the XMLA endpoint.

How to combine two columns in Power BI?

To combine two columns in Power BI, you need to select a new calculated column using the DAX expression.

For example, if you need to add the values of two columns & make the third column, then you can do it by the Following DAX Expression: 

Total=Sheet Name(Col1) +sheet Name(Col2). In the image below, two text columns City & State, are added & concatenated to one new column. In the same way, you can add Numeric columns using DAX.

How to change the date format in Power BI Query Editor?

Select the date column in the table, and go to the “Modelling” tab. Under this tab, we have the “Date” formatting section, select the date type as the date from the drop-down list.

How to edit interactions in Power BI?

Power BI gives you the capability to make interactive charts & reports in your visuals. If it’s a very easy & quick process to activate such interactive visuals, click on the visual you want to use for the interactions to be taken through. Go to the Format tab & then click on edit interactions & there you go. As you can see in the below-depicted image, how the interactions are enabled. You can click on the circles to enable/disable the interaction for a particular chart.

There are basically three edit interaction options in Power BI. They are Filter, Highlight, and None.

Filter: It is used to completely filter a visual or a tile-based on the filter selection of another visual or tile.

Highlight: It is used when we want to highlight only the related elements on the visual or tile, the non-related items can be grayed out.

What is Workspace in Power BI?

Workspace in Power BI is basically an environment that is designed to upload content like reports, dashboards, apps, datasets, etc., to share with a group of people, colleagues, and management. In one workspace, you can add various reports, dashboards, datasets, etc. & add people to view, edit or modify them through the given access. It is like a folder where you keep/save your reports for sharing them further with the concerned group of people in the organization.

To create a workspace in Power BI, you need to log in to your Power BI account from service.

Please follow the below link to log in to the service, and click on the workspaces.

You can also add a report to your Work-Space. You need to first publish it to the Power Bi Desktop using the publish button in the home tab using credentials.

Pictorial view of workspace after logging in to POWER-BI.

When you click on the workspaces & then on your particular saved workspace, you will see your saved reports, dashboards, etc., in it, which you can share with others & export as well.

What is Lock Aspect in Power BI?

Lock Aspect is basically used to lock the proportion of a visual when you try to drag through the corners to resize it. So, for example, if you create a visualization chart & turn the Lock Aspect on – and then try to resize it with the help of your mouse, it won’t change, and it keeps proportions the same as earlier.

You can find the lock aspect in the Format Tab in the Visualization pane. (As shown in the below image).

How to Show Percentage in Power BI?

To show percentage in Power BI we can create a calculated measure/calculated column using DAX calculation & show it in the report visual depending on the requirement of the dataset being analyzed.

In the above pictorial view, we have shown how to calculate profit % by adding a new calculated column & using simple DAX Calculation. You can add a measure as well to show the %, depending on the analysis requirement.

What are the major Building Blocks for Power BI? Explain in detail?

The very first step in using Power BI is to install a Power BI desktop to load & transform Datasets, make the Data model, and establish the relationship between the various tables to connect them for analysis. Create the visuals & Reports, & Publishing them to the Power BI service. Power BI Service is a web-based server to publish & share the reports with people.

Below mentioned are the Main Building Blocks of Power BI:

Datasets: A dataset is a collection of data that needs to be analyzed & used to make visualizations & Create Reports & Dashboards for sharing with the Clients/Management. Power BI can use various kinds of Data-source Files like
Excel sheets, CSV, XML, & connect to various Database servers Like Oracle or SQL server tables.

Visualizations: Visualization is nothing but a visual representation of your data.
Visualizations Pane has various Charts & graphs options like a Donut chart, Line Graph, Pie Chart, Side-by-Side Bar Charts, and Scatter Plots to present your data. Geographical maps can be used to represent Geo MAPS.

Below is an image showing the Visualizations pane on the Power BI desktop.

Reports: A report is made using a collection of visualizations, Like Charts, etc. that are put across one or more pages.
For Example – Sales by Region, State, City Report, Sales/Regional Manager Performance report, Profit by Category/Segment report etc.

Dashboards: The dashboard is made using multiple visualizations, on a single page. We can integrate one or more Specific visualizations into the one-page layer.
A Sales dashboard can have pie charts, Bar charts, geographical maps, Donut charts, etc.

Tiles: In Tiles, we use a single visualization in a single report or on a dashboard.
For Example, Bar Chart in Dashboard or Report.

What is DAX?

Data Analysis Expressions (DAX) is a scripting language that is used to create calculated columns, measurements, and custom tables in Microsoft Power BI. It is a set of functions, operators, and constants that may be used to compute and return one or more values using a formula, or expression. You may utilise DAX to handle a variety of computations and data analysis challenges, allowing you to generate new data from existing data in your model.

What are the purpose and benefits of using the DAX function?

If you’re considering studying DAX, you’ll want to be sure it’s a good investment of your time. The DAX has a number of advantages. In reality, almost every DAX user will tell you that understanding DAX has improved the efficiency and effectiveness of their data analysis. The advantages described in the paragraphs below are cited by the creators.

What is Power Pivot?

Power Pivot is an in-memory data modelling component that allows for highly compressed data storage as well as lightning-fast aggregation and calculations. It’s also included in Excel and may be used to construct a data model in a spreadsheet. Power Pivot may load data into Power Query or load data into Power Pivot by itself. It’s a lot like the SSAS (SQL Server Analysis Services) tabular format, which is similar to Power Pivot’s server version. Power View is an interactive visualisation application that allows users to rapidly and simply build data visualisation in their Excel spreadsheets using a drag-and-drop interface (using the Power Pivot data model).

What is a Power Query?

Power Query is a self-service ETL (Extract, Transform, Load) programme that operates as an Excel add-in. It lets users extract data from a variety of sources, alter the data to fit their needs, and then import it into Excel. The functions are exactly the same regardless of how you got there. The main characteristic of Power Query is that all data import and transformation activities are saved as queries – a series of steps in the proprietary Power Query programming language, which is abbreviated as “M.” Steps can be changed and repeated as many times as needed (update query). Among all the add-ons discussed in this article, this is the most beneficial for a wide spectrum of people. There are a number of jobs that you used to have to either perform horribly with formulae or create macros for, but now you can do them simply and attractively using Power Query. Furthermore, the findings are automatically updated after that. Power Query is clearly out of the competition in terms of “price-quality” and is an essential must-have for any expert Excel user these days.

Difference between Power BI and Tableau?

It’s simple to use Power BI. When the amount of data is reduced, it is speedier and performs better. When working with large amounts of data, Power BI has a tendency to lag. Tableau is capable of fast processing enormous amounts of data. It’s speedier, and it has a lot of options for displaying data. Tableau doesn’t limit the amount of data points in a visualisation or impose row or size restrictions, allowing you to see your data from any angle.

Power BI has an easy-to-use user interface and can be readily connected with other Microsoft applications. The Power BI interface is simple to learn and use. It is simple to use and helps you to work more efficiently. The report view, the data view, and the model view are the three views available on the left side of the canvas in Power BI Desktop. Tableau features an intuitive user interface that allows you to design and configure dashboards to meet your specific needs. It features a welcoming workplace that invites you to explore with data and come up with clever solutions. There are several cards and shelves, a toolbar, a sidebar, a data source page, a status bar, and sheet tabs in the workspace area.

In comparison to Tableau, Power BI supports a variety of data sources but has limited access to other database systems and servers. Microsoft Excel, Text/CSV, Folders, MS SQL Server, Access DB, Oracle Database, IBM DB2, MySQL database, PostgreSQL database, and more are examples. Tableau Software can connect to a variety of data sources and servers, including Excel, Text Files, PDFs, JSON, statistical files, Amazon Redshift, Cloudera Hadoop, Google Analytics, Dropbox, Google Sheets, and Google Drive, among others.

Advanced Level Questions

What are content packs in Power BI?

Content packs are another way of sharing Power BI content with other users. And this Power BI content will include data set, reports, and dashboards. Through content packs, we can easily control the access to your reports when creating a particular content pack; you have the option to keep it available for the whole organization or to a specific group of people. And as for everything in Power BI, you need your account to sign in to access it. One important thing to note here is that this is a Power BI Pro feature which means that you need a Pro license to create, share and consume content packs.

They are created in the Power BI service and can be consumed in the same environment. We cannot use content packs on the desktop. When you are creating a content pack, you have the option of what to include in the pack meaning- it allows you to select the particular dashboard and all its related reports and datasets together in one content pack. One more thing to note here is that if we get access to a content pack, it is read-only access, so we will not be able to alter any original content. If we want to work on the same data set, it gives us the option to create a copy and build our own analysis around the same.

How to use Power BI in excel?

To use Power BI in Excel, there is an Analyse in Excel option for every report in the Power BI service. To use it, you will need to enable editing and enable content for the report for the first time.

So, what this option provides us is that it gives us the underlying data set of our Power BI report. It comes as a data connection in excel. And we get to play with the data in excel. It is up to us how we analyze the same data, either through pivot tables, charts, etc. By default, when the data is extracted in excel for any report- it gives a Pivot table by default.

How to change x-axis values in Power BI?

In Power BI, we can change the x-axis dynamically. There are a few steps we need to follow to achieve the same.

  1. Creating a separate table with the required dimension names.
  2. Once the above table is created, we add a slicer to the report with the field that has all the dimension names.
  3. Next, we create a calculated measure that will give us the selected dimension from the slicer. We use the SELECTEDVALUE() function to get the value.
  4. Next, we create another calculated column, which based on the value from the above measure, will select the particular column from the data. 
  5. When creating the visual- the column created from step 4, should be dragged for the x-axis and the y axis remains the same.
  6. Now as you will select the different field names, the x-axis will change accordingly.

How to connect SQL Server to Power BI?

SQL Server database connection is available in Power BI. Steps to be followed are:

  1. On Power BI desktop go to get the data option in Home. Under Get Data, we have SQL Server as an option or when clicked on More- you can select SQL Server database from the list.
  2. When we click on connect- it asks us to provide a few basic details related to the connection. Server info, Database info, which is optional, Data connectivity mode(either a direct query or import mode) 
  3. Once the above information is furnished, we can click on OK to connect.
  4. In case you need to write a specific query to load the data, you can do so under Advanced options.
  5. Without the above option, if you click on OK, a window opens that asks us for the credentials for the login to the database. We have the option to provide the specific credentials or use a Windows login. It depends on how you have been given access to the database. Current credentials refer to your windows credentials. Alternate refers to Specific to the database.
  6. Once access is verified, you get a window with all the databases on the server shown in the left and right pane showing a preview of the data from any table selected on the left.
  7. Once you select the table to use, you can see three options at the bottom – load, edit and cancel. The load will directly load the data. Edit will open Power Query Editor and will allow you to do transformations in the data before loading.
  8. Once data is loaded, you can use it to create visuals as per your choice.

Which two cross filter directions are available in Power BI table relationships?

When a relationship is created between two different tables in Power BI, then the relationship asks us the cross-filter direction. There are two options available for cross-filtering.

  1. Single – When the cross filter direction is single, then the filtering between tables happens from left table to right table. It is the default setting. The first table can be used to filter the data in the second table.
  2. Both – When the cross filter direction is both, then the filtering between the tables will work in both ways. Either table can be used to filter the other table.

How to remove null values in Power BI?

In order to remove null Values from the data can be removed in the Power Query Editor window on the Power BI desktop. There are multiple ways to remove null values:

  1. Sort the data respective to the column in which we have nulls. If removing the null rows does not affect your data, then you can delete them right away.
  2. Another option is to use the Replace Values option in the Editor window. You can replace the null values with 0 or any other relevant value.

How to connect JIRA to Power BI?

We connect to JIRA using JIRA content packs and JIRA Rest API. There are a few steps involved.

  1. In order to connect JIRA to Power BI, we need an API token that can be generated from the Atlassian link.
  2. Next, we download and install the Jira content pack. Then the API token is added to the Jira content pack.
  3. Once the connection is set, it automatically creates a workspace with a dashboard and a set of reports to visually analyze the data.

What is OData feed in Power BI?

OData stands for open data protocol. It is usually used to pull data from websites, SharePoint. It helps in getting data from URLs. It basically helps to extract data from a URL without getting into the details of URL-specific parameters like request, response, HTTP methods, etc. It takes care of these things in the back end, leaving us to focus on pulling the data and performing transformations, and creating useful analysis.

How Many Filters are there in Power BI?

Power BI provides us with a variety of options to filter our reports, data tables, and visualization. These filters are not saved as a part of the report. Whenever we open a report, then we can use visual filters but there is no option to store the filter in the saved report. Below mentioned is the list of types of filters available in Power BI.

Page-level Filters: Page-level filters work at the report Page level. In reports, there might be different pages. These available pages in the same report can have different filters at the page-level filters.

Visual-level Filters: Visual-level filters work only for an individual visualization, by reducing the amount of data that the specific visualization can see. The visual-level filters have the capability to filter both data and calculations.

Report-level Filters: The report level filters work on the entire report, which filters all the pages and the respective visualizations included in the particular report.

How to Filter Top N in Power BI?

Power BI provides us with a great option, filter top N in visual data. You can go to the Filter Pane, then go to the filter on this visual & select option “Filter type” as top N & show items as top 3, 5, and 10. For example, the by value is the amount you would prefer to filter by, let’s say sales amount or profit amount. If we want to keep it dynamic, then we can create a parameter table named Top N with a measure as Top N Value that gives us the selected value. For that scenario, we can use the what-if parameter feature provided by Power BI. In the visual shown below, you can see how the Top N can be created. I have tried showing the top 5 sales figures by category. 

What is Slicer in Power BI?

Slicers are a way of filtering the data from a chart or reports prepared. Suppose you want your colleagues to be able to look at overall sales metrics, but also want to highlight performance for each individual regional manager/Region-wise Filtering. You can create a list or dropdown as well using Slicer, Date & number Slicers are also available to separate reports or comparative charts, Date wise filtering of the Data. While applying Filters in the Reports You have two options to either use the Filters pane & add the Filters or use slicers from the Visualizations. Slicers are another way of filtering. Slicers basically narrow down the portion of the dataset which is shown in the other report visualizations. Attached below is a Representation of Slicer, For every selected Filter in the slicer, the value of Sales & the count will change. 

How to publish Power BI reports to the Web?

To publish reports to Power BI Web or Service, you need to click on the publish option available in Power BI Desktop. The report generation environment using service credentials. Log into Power BI Service & click on the report that you published. Once you open the report, just click on the File menu option and select the option Publish to web. Then you will get information in a message box that says, this step will create a link and embed code for you to share with the world through a website or even an email. Now click on create embed code as shown in the below image.

After clicking on publish, you will see the embed code & a link to share through email. You also have an option to choose the size of the screen for this embedded code.

What is Drill through in Power BI?

Drill through is one of the extremely powerful features provided in the Power BI Self-service business intelligence tool. It gives the user the ability to see data and the information in a more detailed way giving a clear picture. Drill through takes the user to a report which is relevant to the data which is being analyzed. For example, a tabular report that shows Profit by Country can allow the users to click on it and open a grid of the same data, or a Pie Chart, etc., for representing the data in visual form. It is called “drill through” because it is a specific feature that allows the user to pass from one report to another while analyzing the same data. It helps users to analyze the data with different features and display it through different visualization methods. If you click on the Fields sections of visualization Pane & scroll it down, you will find a Drill through the option to select for Cross Reports or only for Page Level. You can select the column when the data should be Drilled through.

What is Forecast in Power BI?

Forecast in Power BI helps its users to Forecast for the next period. To use the forecasting feature we need to use the Analytics tab, The Analytics pane allows us to add the dynamic reference lines to the visuals we create & provide a focus to the important trends in the data or the key insights. In The Visualizations pane, when we Click on the Line chart it creates a chart box in the canvas. Nothing will be displayed in the canvas chart until we add the required visualization requirements.

You can Find a Forecast in the Visualizations Pane of Power BI Desktop. For Forecasting, we need to go to the analytics pane so we can see a Forecast option.

Just click on Add, & then set the forecast length to 2 months 5 Years or any time point you want to forecast for, with a Confidence Interval, and then click on Apply. We can see that a forecast line is set after our data ends and it is in the shaded grey area which is our confidence interval.

Below are the images showing the Forecast location in the Viz. Pane.

What is Bookmark in Power BI?

Bookmark is a capability provided by the POWER BI that saves the state of the page exactly as it is at the time of saving a bookmark. This states that you can later select the saved bookmark and see the page in the same state in which you had saved it. Hold on. Bookmark is not at all a screenshot. It is actually a fully interactive report page in Power BI, with the same state it was saved.

When we create a bookmark, the below-mentioned elements will also be saved along with the bookmark:

It will have the added Filters, the current report page, and the Slicers which were added as is, which includes the slicer type a Dropdown or a list), Visual selection state, for an example-highlight filters/Cross Filters, etc., Drill location, The Spotlight & focus modes of a visible object, Sort order, and the Visibility of any object (also using the selection pane).

If you want to create a bookmark, you need to configure a report page first for appearing/saving in the bookmark. At last, Once you are ready with your report page and the visuals arranged the way you want them to be, just select Add from the Bookmarks pane to add a bookmark, and that’s it, you have created your bookmark.

Have a look at the image below showing where the bookmarks option is found in Power BI Service & what are the two types of bookmarks.

What is Row-level security in Power BI?

Row-level security restricts the access of certain data to the given users. Filters are used to restrict the data access at the Row level.

In the Power BI Desktop, once you are done with the Report creation, You can define the roles & rules, these defined roles are also published to the Power BI service, once the Reports are published to the service. You can use the below steps to create the security roles.

Once you Import the data into your Power BI Desktop, or if it is a Direct query then configure a connection. 

Go to the Modeling tab, select Manage Roles & select create. Give a name to your Role.

Now select a table (Here Sales Territory Column in the Table Dim Sales Territory) in which you want to create rules by using the DAX.

Once selected the DAX click on the check box & there you go. For example, you have the sales data of different countries & you want that a particular country should see the data related to that country only, Once you create the roles, you can view them by going to view roles & then you will have the data only about the Role created as Country USA. (You can see this Example for ref. shown in the below images).

What is Power BI Data Gateway? How can we use it?

Power BI Data Gateway is basically a software which is used to access the data which is connected to on –Premise- network from cloud.

If you have to get connected to On-Premise data from a cloud /web based app then it is requested through the Gateway. The access is granted through user authentication.

It basically does not transfer the data from the source to the client platform, it just connects the platform to on premise data source for the direct access.

To use Data Gateway, you need to first install it in your system, Add the users to this who will be accessing the data from on-premise sources. Give authentication & request. Now get connected to the On-Premise Data sources.

What is GetData in Power BI?

When you use Get Data in Power BI, it automatically creates a dataset. You may connect to and import data from a template app, a file, or a live data source with Get Data. A dataset comprises data-source credentials as well as information about the data source. It often includes a subset of data transferred from the data source as well. You’re frequently looking at data in the dataset while creating visualisations in reports and dashboards.

What are custom visuals in Power BI?

Many graphics are included with Power BI out of the box. These Power BI visuals are accessible in both Power BI Desktop and Power BI service’s visualisation panes and may be used to create and update Power BI material. You may also create your own Power BI graphic, which you, your company, or the whole Power BI community can utilise. Power BI visualisations are software packages that incorporate code for visualising data. Anyone may design a custom visual and save it as a.pbiviz file, which can subsequently be imported into a Power BI report.

How does SSRS integrate with Power BI?

Power BI Report Server, as mentioned in the previous piece, can host Power BI reports on-premises. Another connection exists between SQL Server Reporting Services and Power BI; this connection allows tiles from SSRS reports to be pinned to a Power BI dashboard and get scheduled updates from the SQL Server agent. When SSRS reports are integrated into the Power BI service, a link will be created between the Power BI dashboard and the SSRS detailed reports.

What is query folding in Power Query?

The capacity of a Power Query query to construct a single query statement to extract and transform source data is known as query folding. For efficiency, the Power Query mashup engine tries to accomplish query folding wherever feasible.

Import model tables: In terms of resource use and refresh duration, data refresh for Import model tables (Power Pivot or Power BI Desktop) will be efficient.

Tables in the DirectQuery and Dual storage modes: Each DirectQuery and Dual storage mode table must be built on a foldable Power Query query.

How can we refresh Power BI Reports once they are published on the cloud? 

The Power BI Reports can be refreshed through Data Gateway once they are published on the cloud. If the reports are on the Power BI Site/Web, then we can use Power BI personal Gateway & if the reports are published on share-point, etc., we can use data management gateway. 

What are the different kinds of Data refresh options available in Power BI?

The data of the published reports can be refreshed using four different ways in Power BI. (Package Refresh, Tile refresh, Visual Container Refresh, Model Refresh). 

Package Refresh: It refreshes the report by updating the data from one drive or SharePoint. Here, the data is not refreshed through the source. Rather, it refreshes the data from the cloud location.

Tile Refresh: In this type of Refresh, the cache data is refreshed or all the tiles in a Dashboard. Tile Refresh happens automatically after every 15 Minutes.

We also have an option to select the refresh Dashboard tiles from the (…) Ellipses option on the dashboard.

Visual Container Refresh: In this type of Refresh, the cache of the entire visual container of a Report is refreshed.

Model or Data Refresh: In this type of refresh, the data in a report is basically refreshed from the Original data source itself. Data Refresh is done using the Data Gateway permissions in Power BI Service. In this refresh, there are two types of refresh is done: Schedule Refresh for a later time or Refresh Now

Conclusion

We hope the above Power BI interview questions guide you and help you prepare for any Power BI interview. Along with these, we would recommend you to brush up your Power BI fundamentals. This will help to grasp the concept explained through the answers in a better way, and you will be more confident. All the very best!

You can also take up the Data Visualization With Power BI Free Online Course offered by Great Learning Academy and upskill today!

[ad_2]

Leave a Comment