Top 15 Power BI interview Questions with detailed Answers

1. What are the filters available in Power BI Report?

There are four different filters available in Power BI such as Visual level filters, Page level filters, Drill through filters and Report level filters.

Visual level Filters: Visual level filters work on individual visualization and the relevant value/data can be changed on the page if we click on particular visualization entity/data. Visual level filters can filter both data and calculations.

Page level Filters: As the name implies, this filters work at the report page level. A report can have multiple pages and each page have different page level filters.

Report level Filters: This filters are applicable for entire report, filtering all pages and visualizations included in the report.

Drill through Filters:  This filter can be applied for specific data or entity, to focus on the particular data which can be navigate to another page using Drill through Filters.

2. How will you refresh the data in Power BI report?

The Power BI dash board, reports are consist of data set to populate the visualization data and measures once we published from Power BI desktop. The data set will be created once we have established the connection using Get Data method.  

When we are establishing the connection in Power BI, we can choose anyone connectivity mode such as Direct Query or Import.

If we have chosen the Direct query mode then when we are interacting with visualization in Power BI, the queries will be sending from Power BI to database directly. To enhance performance, dashboards tiles are cached and periodically refreshed and also we can manually refresh the cache. We have seven different scheduled cache refresh in Power BI service to refresh the data such as 15 minutes, 30 Minutes, 1 hour, 2 hours, 3 hours, Daily and Weekly.

If we have chosen Import mode, we can schedule the data refresh periodically. We can find the data set under the work space in Power online, there we can choose either refresh now or scheduled refresh option. We have two different refresh frequency such as daily and weekly for import mode connectivity.

Types of Data Refresh in Power BI

There are four main types of refresh that happen within Power BI. Package refresh, model/data refresh, tile refresh and visual container refresh.

Package Refresh

This synchronizes your Power BI Desktop, or Excel, file between the Power BI service and OneDrive, or SharePoint Online. This does not pull data from the original data source. The dataset in Power BI will only be updated with what is in the file within OneDrive, or SharePoint Online.

Model/data refresh

This is referring to refreshing the dataset, within the Power BI service, with data from the original data source. This is done by either using scheduled refresh, or refresh now. This requires a gateway for on-premises data sources.

Tile refresh

Tile refresh updates the cache for tile visuals, on the dashboard, once data changes. This happens about every fifteen minutes. You can also force a tile refresh by selecting the ellipsis (...) in the upper right of a dashboard and selecting Refresh dashboard tiles.

Visual container refresh

Refreshing the visual container updates the cached report visuals, within a report, once the data changes.

3. What are the connectivity modes available in Power BI?

Import and Direct Query connectivity modes available in Power BI which can be configurable while establishing the connection.

Import: the selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh.

DirectQuery: no data is imported or copied into Power BI Desktop. When you interact with a visualization, queries are sent from Power BI directly to the database.

For relational sources, the selected tables and columns appear in the Fields list. For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you’re always viewing current data.

4. How will you share your Power BI report to client?

There are two ways to share the Power BI report to client such as embed code and content pack option.

Content Pack:
Create a content pack in Power BI which can be shared to specific group or entire organization. The content pack can have Dashboards, Reports and relevant Data sets.

Content packs for services are pre-built solutions for popular services as part of the Power BI experience. A subscriber to a supported service, can quickly connect to their account from Power BI to see their data through live dashboards and interactive reports that have been pre-built for them.

Embed Code: We have an option in Power BI online that publish to web which will generate a link address for the particular Power report. We can share that link to our client to view Power BI report without any sign in/login formalities.

5. What are the gateways available in Power BI?

There are two different gateways available in Power BI such as On-premises data gateway and On-premises data gateway(personal mode).

On-premises data gateway:

1. Multiple users can have access control on data source.
2. Supports schedule refresh and live query for Power BI.
3. Support live connection to Analysis Services.
4. Monitoring and auditing for gateway and data source will be available.
5. Can be used by Power BI, Power Apps, Logic Apps and Microsoft Flow.

On-premises data gateway (Personal mode):

1. Single user can have access control on data source.
2. Support only on Power BI not in PowerApps, LogicApps and Microsoft Flow.
3. Only schedule refresh is supported.

6. What are the different components of Power BI application?

Power BI Desktop - Power BI is a desktop application used to develop and publish the report to Power BI cloud Service. Power BI service offering advanced data exploration, modelling, shaping, and report creation with the use of highly interactive visualizations.

Power Query - Power query is an ETL Tool used to shape, clean and transform data using intuitive interfaces without having to using code.

Power Pivot - Power Pivot helps in analysing the data and allows to defining the data model for in-memory analytics.

Power View - Power View used to analyze and represents data as an interactive data visualization.

Power Map - Power Map helps in bringing the information with interactive geographical visualization.

Power BI Q & A - Q&A is a natural language based experience for interacting with data as part of the Power BI for Office 365 offering.

Data Management Gateway - Gateway acts a bridge between on-premises data sources and Azure cloud services.

7. What are the report components available in Power BI?

There are five components available in Power BI and those are listed as below:

1. Visualizations
2. Data sets
3. Reports
4. Dashboards
5. Tiles

Visualizations: Visualization is a visual representation of data in Power BI report. For instance, we have a large size of sales related database and we cannot go through each and every tables and columns to understand the business statistics. Power BI visualization is a best way to understand the business statistics in single point of view.
Example: Bar Chart, Pie Chart, Donut Chart, Graphical presentation of geographical map and etc.

Data Sets: Data set is a collection of data from various data source that Power BI utilize to built the visualizations.
Example: SQL database, Excel, Analysis Service and etc.

Reports: Report is a collection of pages and visualizations which is built it from data set.
Example: Sales report by region wise and year wise.

Dashboards: Dashboard is single layer presentation of multiple visualization. For instance, we can integrate one or more visualizations in to one page layer.
Example: Dashboard can have pie charts, geographical maps and bar charts.

Tiles: Tile is a single visualization in a report or on a dashboard.
Example: Pie Chart in Dashboard or Report.

8. How to apply data security in Power BI report?

Power Bi provides Row-level security (RLS) roles to models.

A DAX expression can be applied on a table filtering its rows at query time.

Dynamic security involves the use of USERNAME functions in security role definitions.

Typically a table is created in the model that relates users to specific dimensions and a role.

9. What is Natural language search option in Power BI?

Q&A is a natural language based experience for interacting with data as part of the Power BI for Office 365 offering.

As you type your question, Q&A picks the best visualization to display your answer; and the visualization changes dynamically as you modify the question. Also, Q&A helps you format your question with suggestions, auto-complete, and spelling corrections.

When you finish typing your question, the result is the exact same chart that we saw in the report. But creating it this way was much faster!

10. Can we combine two different data sources in same Power BI dashboard?

Yes. It is possible in Power BI. In Power BI Online, we can combine visualization from different reports in your Power BI account into one single dashboard (using pin) irrespective of the data sources.

To do so in single report, we can use Desktop tool having 'Merge Queries' functionality in Query Editor to achieve the same.

11. What is the difference between Power BI Desktop and Power BI online?

Power BI Desktop:

Power BI Desktop is a windows application which can be run on our local computer. Power BI desktop can be used to develop the reports, connect with variety of data sources from our computer to the data in the local computer or the data in the server. We can publish the report from Power BI desktop to Power BI online once completed the development.

Power BI Online:

Power BI is a cloud based solution. We can use web browser to connect with Power BI online. We can perform the same development work on Power BI online but some limitations there.

12. What is DAX and the use in Power BI?

DAX stands for Data Analysis Expression is used to do basic calculation and data analysis from the data. In Power BI, we need to calculate the value at runtime based on the user inputs and the DAX calcuation can be assigned to measure name.

DAX Features:

1. DAX formula used column/field level not in row level.
2. DAX formula can not modify or insert data.
3. DAX formula output can be stored on measure name.

DAX Syntax:

13. What is the difference between SUM and SUMX in DAX function?

SUM is an aggregate function and it returns the total value of column data. The Syntax is SUM(<Column>). It works like the sum function in excel however Excel works on cell references or cell ranges and DAX works in columns. SUM operates over a single column and has no awareness of individual rows in the column (no row by row evaluation).

SUMX is an Iterator function and it returns the running total value of column data. The Syntax is SUMX(<table>, <expression>). SUMX can operate on multiple columns in a table and can complete row by row evaluation in those columns.

Say for example, we have 5 integer rows in a field and each row has value 3. If we use SUM function, it will return simple calculation value as 5 * 3 = 15 where SUMX returns row by row calculation like as below.

Row Num	Value	SUMX
1	3	3
2	3	6
3	3	9
4	3	12
5	3	15

14. Differentiate the data modeling between Power BI Desktop and Power Pivot for Excel?

Power BI Desktop is supporting bi-directional cross filtering relationships, security, calculated tables, and Direct Query options..

Power Pivot for Excel is having single direction (one to many) relationships, calculated columns and supports import mode only. Security roles cannot be defined in Power Pivot for Excel.

15. What is query parameters and Power BI templates??

Query parameters can be used to provide users of a local Power BI Desktop report with a prompt, to specify the values they are interested in.

The parameter selection can then be used by the query and calculations.

PBIX files can be exported as Templates (PBIT files).

Templates contain everything in the PBIX except the data itself.

Parameters and templates can make it possible to share/email smaller template files and limit the amount of data loaded into the local PBIX files, improving processing time and experience.

Also see below articles
Top 20 SSIS Interview Questions with detailed answers
Top 30 SQL Server DBA Interview Questions and Answers