Data visualization has come a long way from creating pie or graph charts in MS Excel. Since the launch of Power BI by Microsoft a few years back looking into data had never been this interactive and fun. The demand for Power BI as a business intelligence/data visualization tool has only risen in the past few years and a lot of companies hire individuals to post a thorough knowledge search of this tool via interviews.

So, here some Power BI Interview Questions that the user might find helpful on the user quest to facing the interview.

Q1. What is a Power BI?

Power BI is a cloud-based data-sharing environment. Once the user has developed reports using Power Query, Power Pivot, and Power View. The user can share his/her insights with colleagues. This is where Power BI enters the equation. Power BI allows the operator to upload Excel workbooks into the Power BI cloud and share them with a chosen group of co-workers. Not only that, the colleagues can interact with the user’s reports to apply filters and slicers to highlight data. It is a simple way of sharing the user’s analysis and insights from the Microsoft cloud with the rest of the department.

Power BI features allow the user to:

• Share presentations and queries with peers.
• Update the Excel file from data sources that can be on-site or in the cloud.
• Display the infographic on multiple devices like desktops, tablets, and HTML 5-enabled smartphones that can use the Power BI app.
• Query the user data using natural language processing (or Q&A, as it is known)

Q2. What is self-service business intelligence?

Self-Service Business Intelligence (SSBI)

SSBI is a tactic to data analytics that allows trade users to filter, segment, and analyze their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI).

SSBI has made it stress-free for end-users to access their data and craft various visuals to get healthier business insights.

Anybody who has a rudimentary grasp of data can generate reports to build instinctive and shareable dashboards.

Q3. What is the Power BI Desktop?

Power BI Desktop is a free desktop application that can be installed right on the user’s laptop/desktop. Power BI Desktop works together with Power BI services by offering data exploration, shaping & modeling and also, aid the user in creating a report with highly interactive visualizations. The user can save the work to a file or publish the data and reports right to the Power BI site to share with his/her colleagues.

Q4. Describe the data sources, that Power BI can connect to?

The list of data sources for Power BI is massive, but it can be clustered into the following:

• Files – Data can be pulled in from Excel, Power BI Desktop files, and Comma Separated Values.

• Content Packs – It is a compendium of related documents or files that are stored as a group. In Power BI there are two categories of content packs, primarily one is from service providers like Google Analytics, Marketo, or Salesforce and secondly, the one is created and shared by other colleagues in the company.
• Connections to databases and other datasets such as Azure SQL, Database and SQL, Server Analysis Services tabular data, etc.
• Describe the diverse filters in Power BI Reports?

Power BI provides a variety of options to filter, report, data, and visualize. The following is the list of Filter types.

• Visual Filters – These work on an individual visualization’s, decreasing the amount of data that the visualization can show. Also, visual filters can compartmentalize both calculus and data.

• Page Filters – These filters work at the report-page level. Distinct pages in the same report can have unique page-level filters.

• Report Filters – These work on the complete report, sorting all the pages and visualizations incorporated in the report.

Power BI visuals have an interactive feature, which makes filtering a report easy. Visual interactions are insightful but come with some restrictions:

• The filter used is not saved as part of the report – Whenever the user launches a report, the user can use visual filters but would not be able to store the filter in the original report.
• The filter is always visible – Sometimes the user intends to use a filter for the entire report, but the user does not wish any visual indication of the filter being applied.

Q5. What is the format available in Power BI?

• Power BI Desktop – for desktop/laptop
• Power BI service – Online SaaS
• Power BI mobile app – for android and iOS devices

All of these may be used in conjunction. For example, the user might create a report on the desktop and then publish and share it online so that the colleagues could view it on their mobile devices.

Q6. What are content packs in Power BI?

Content packs are prefabricated solutions for popular features as part of the Power BI experience. A user with access can link to their profile from Power BI to see their data through live dashboards and interactive reports that have been fabricated for them. Microsoft has published content packs for services like Salesforce.com, Marketo, Adobe Analytics, Azure Mobile Engagement, CircuitID, etc.

The corporate content pack gives the employees, BI professionals, and system integrator the means to construct their content packs and to share purpose-built dashboards, reports, and datasets within their company.

Q7. Describe the building blocks of Power BI?

• Visualizations – Visualization refers to a chart, graph, or similar graphic depiction of data.
• Datasets – A dataset is the group of data used to generate a visualization such as a column of sales figures. Datasets can be merged and cleaned from distinct sources using built-in connectors.
• Reports – A report is a collection of visualizations of one or more pages; for example, charts, graphs, and maps can be fused to create a report. 
• Dashboards – A dashboard lets the user share a one-page visualization with others, who can then interact with the user’s dashboard.
• Tiles – A tile is a visualization on the user’s dashboard or in the report. As the creator, the user has complete access to move the tiles around.

Q8. Describe DAX?

Data Analysis Expressions (DAX) is a compilation of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps the user create new information from the data obtained. 

To do the basic calculation and data analysis on data in power pivot, we use Data Analysis Expression (DAX). It is a formula language used to compute the calculated column and field.

• DAX works on column values.
• DAX cannot modify or insert data.

We can create computed columns and measures with DAX but we cannot evaluate rows using DAX.

Q9. What are the most shared DAX Functions used?

Below mentioned are some of the most widely used DAX function: 

• SUM, MIN, MAX, AVG, COUNTROWS, DISTINCTCOUNT
• IF, AND, OR, SWITCH
• ISBLANK, ISFILTERED, ISCROSSFILTERED
• VALUES, ALL, FILTER, CALCULATE,
• UNION, INTERSECT, EXCEPT, NATURALINNERJOIN,
• NATURALLEFTEROUTERJOIN,
• SUMMARIZECOLUMNS, ISEMPTY,
• VAR (Variables)
• GEOMEAN, MEDIAN, DATEDIFF

Q10. How is the FILTER function used?

The FILTER function yields a table with a filter condition applied for each of its source table rows. The FILTER function is rarely used alone; it is generally used as a parameter to other functions such as CALCULATE. 

FILTER is a mediator and thus can negatively impact performance over large source tables.

Complex categorizing rationality can be applied such as referencing a measure in a filter expression.

FILTER(MyTable,[SalesMetric] > 500)

Q11. What is the common table function for grouping data?

• SUMMARIZE()
• Main group by function in SSAS.
• The recommended routine is to specify the table and group by columns but not by metrics. The user can use ADDCOLUMNS function.
• SUMMARIZECOLUMNS
• Create a New group by function for SSAS and Power BI Desktop; more efficient
• Specify group by columns, table, and expressions.

Q12. What are some benefits of using Variables in DAX?

Below are some of the benefits: 

• By announcing and calculating a variable, the variable may be reprocessed multiple times in a DAX expression, thus avoiding additional queries of the source database.
• Variables can make DAX expressions more intuitive/logical to predict.
• Variables are only measured to their query, they cannot be shared among measures, queries, or be defined at the model level.

Q13. How would the user create trailing X month metrics via DAX against a non-standard calendar?

The solution will involve:

1. CALCULATE function to control (take over) filter context of measures.
2. ALL to remove existing filters on the date dimension.
3. FILTER to identify which rows of the date dimension to use.

Alternatively, CONTAINS may be used:

CALCULATE(FILTER(ALL(‘DATE’),…….))

Q14. What are the three fundamental concepts of DAX?

• Syntax – The Syntax includes functions such as SUM (used when the user wants to add figures). If the syntax isn’t correct, the user will get an error message.

• Functions –  These formulas are used as arguments in a particular order to perform a calculation, akin to the functions in Excel. The different types of functions are time intelligence, logical, parent/child, mathematical, date/time, statistical, etc.

• Context– There are basically 2 categories: –

1. • row context and
   • filter context

Every Time a formula has a function that applies filters to identify a single row in a table, row context comes into play. When multiple filters are employed in a calculation that determines a result or value, the filter context comes into play.

Q15. What are some of the most common sources for data in the Get Data menu?

Power BI datasets, MS Excel, SQL server, and analysis services.

Q16. What is grouping, and how would the user use it?

Power BI Desktop groups the data in the user’s visuals into pieces. The user can however define the user’s groups and bins. For this use Ctrl and click to select multiple elements in the visual. Right-click on one of those elements and from the menu that appears, opt the Group. In the Groups window, the user can create new groups or modify existing ones.

Q17. Describe responsive slicers.

On a report page, the user can resize a responsive slicer to different sizes and shapes, and the data contained in it will be rearranged to match. If a graphic becomes small to be useful, an icon representing the visual takes its place-making judicious use of space on the report page.

Q18. What are the main components of the Power BI toolkit, and what do they do?

• Power Query: lets the user discover, access, and consolidate info from different sources
• Power Pivot: a modeling tool
• Power View: a performance tool for creating charts, tables, etc
• Power Map: lets the user create geospatial representations of the user’s data
• Power Q&A: Allows the operator to use natural language to raise queries, i.e. “What were the total sales last week?”

Q19. What is Power Pivot Data Model?

It is a specifically designed model that is made up of data types, tables, columns, and table relations. These data models are normally assembled for keeping data for a private business entity.

Q20. What is the velocity in-memory analytics engine used in Power Pivot?

The programming behind the power pivot is the xVelocity in-memory analytics engine. It is capable of handling a significant volume of data with ease as it stores the data in the columnar database and in-memory analytics which results in faster processing of data as it loads the entire data to the RAM.

Q21. What are some of the differences in data modeling between Power BI Desktop and Power Pivot for Excel?

Here are some of the differences:

• Power BI Desktop supports two-way dual relationships, security, calculated tables, and Direct Query options.
• Power Pivot for Excel has one direction relationship, calculated columns only, and supports import mode only.

Q22. Can we have more than one active relationship between two tables in the data model of a power pivot?

More than one active relationship between two tables is NOT possible. However, the user can instill more than one relationship between two tables but there will be only one active relationship and many inactive relationships.

Q23. What is Power Query?

Power query is an extract, transform & load tool used to shape data using intuitive interfaces without the need of using coding. It helps the user to:

• Import Data from multiple sources i.e. files, databases, big data, social media data, etc.
• Join and attach data from multiple data sources. 
• Model data as per the requirement by deleting and adding data.

Q24. What are the data destinations for Power Queries?

There are majorly two destinations for output we can derive from power query:

• Load to a table in a worksheet.
• Load to the Excel Data Model.

Q25. What is query folding in Power Query?

Query folding is the procedure where the steps defined in the Power Query/Query Editor are translated into SQL and executed by the source database rather than the operators’ device. It is imperative for administering performance and scalability making the best use of the limited resources on the client’s machine.

Q26. What is some common Power Query/Editor Transforms?

• Changing Data Types
• Filtering Rows
• Choosing/Removing Columns
• Grouping
• Splitting a column into multiple columns
• Adding new Columns etc.

Q27. Is the combined use of Power Query/Query Editor and SQL possible?

Certainly, an SQL announcement can be defined as the source of a Power Query/M function for additional processing/logic. This is a good practice to ensure that an efficient database query is passed to the source and any unnecessary processing and complexity are avoided by the client machine and M function.

Q28. What are query parameters and Power BI templates?

• Query parameters may be used to offer the operators of a regional Power BI Desktop report with a notification, to specify the values that they might be interested in.
• The parameter selection may then be used by the query and calculations.
• PBIX files can be transferred as Templates (PBIT files).
• Templates encompass everything in the PBIX except the data itself.
• Parameters and templates can make it possible to share/email tinier template files and restrict the amount of data that is loaded on to the local PBIX files, maximizing processing time, and experience.

Q29. Which language is used in Power Query?

An innovative programming language is used to power a query called M-Code. It is simple to use and related to other languages. M-code is case sensitive language.

Q30. What is the need for a Power Query when Power Pivot can be used to import data from the most frequently used sources?

Power Query is an ETL (Extract, Transform, Load) tool that operates as an Excel add-in. It permits operators to extract data from numerous sources, manipulate the said data into a form that suits their needs, and load it on to Excel. It is the most recommended routine to use Power Query over Power Pivot as it not only allows the user to load the data but also influence it as per the user’s needs while uploading.

Q31. Explain the term “M language”.

It is the programming language used in Power BI. It’s a functional, case-sensitive language that is a lot similar to other programming languages which are easy to implicate.

Q32. Describe the difference between visual-level filters, page-level filters, and report-level filters? 

• Visual filters filter data within a single visualization.
• Page filters work on the complete page in a report, and different pages may have separate filters.
• Report filters, categorize all the visualizations and pages in the report.

Q33. How does the Schedule Refresh feature work?

The user can configure an automatic refreshing of data daily or weekly and at different times. The user can schedule only one refresh maximum daily unless the user has Power BI Pro. In the Schedule Refresh section, use the pulldown menu selections to choose the frequency, time zone, and time of day.

Q34. What data is required to generate a map in Power Map?

Power Map can display only geographical visualizations. Thus, data about the location required e.g. city, state, country or latitude, and longitude, etc.

Q35. What is the Power Map?

Power Map is an Excel add-in that provides the user with a powerful set of tools to help the user visualize and gain insight into large sets of data that have a geo-coded component. It can help the operator generate 3D visualizations by scheming up to a million data points in the form of column, heat, and bubble maps on top of a BING map. In certain scenarios, if the data is time-stamped, it can also produce interactive views that display how the data changes over time.

Q36. What is the principal requirement for a table to be used in Power Map?

For data to be consumed in power map there should be location data like:

• Latitude & Longitude
• Street, City, Country/Region, zip code, etc which can be geolocated by BING

The initial requirement for the table is that it should consist of distinctive rows. It must also include location data, which can be in the form of a Latitude & Longitude, although this is not a requirement. The operator can use Street, City, Country/Region, Zip Code/Postal Code, and State/Province which can be geolocated by Bing.

Q37. What are the data sources for Power Map?

Data sources may differ from being present in Excel format to multiple external sources. To prepare the user data, make sure all of the data is in an Excel table format, where each row represents a unique record. Using expressive labels also makes the value and category fields available to the user when the user designs the user tour in the Power Map Tour Editor pane.

To be able to use a table structure that precisely symbolizes time and geography inside Power Map, include all of the data in the table rows and use descriptive text labels in the column headings, like this:

• In case the user wishes to load the user data from an external source:
• In Excel, click Data > the connection the user wants in the Get External Data group.
• Adhere to the steps in the wizard that pops up.
• In the last step of the popup box make sure to add this data to the Data Model to have it checked.

Q38. What is Power View?

Power View is a data visualization technology that allows the operator to create cooperative charts, graphs, maps, and other infographics that bring the user’s data to life. Power View is widely accessible in Excel, SharePoint, SQL Server, and Power BI.

The following provides comprehensive detail about the different visualizations available in Power View:

• Charts 
• Line charts 
• Pie charts 
• Maps
• Tiles 
• Cards 
• Images
• Tables
• Power View
• Multiples Visualizations 
• Bubble and scatter charts 
• Key performance indicators (KPIs) 

Q39. What is the Power BI Designer?

It is a stand-alone application where we can make Power BI reports and then upload them to Powerbi.com, it does not require Excel. It is an amalgamation of Power View, Power Pivot, and Power Query.

Q40. How can we refresh our Power BI reports once uploaded to the cloud (Share point or Powebi.com)?

Yes, the user can refresh his/her reports through Data Management gateway(for SharePoint) and Power BI Personal gateway(for Powerbi.com)

Q41. What are the different types of refreshing data for our published reports?

There are four main types of options for a refresh in Power BI.

Package Refresh – This feature harmonizes the user’s Power BI Desktop or Excel file between the Power BI service and OneDrive or SharePoint Online. However, this does not extract data from the original data source. The dataset in Power BI will only be revised with the data in OneDrive or SharePoint Online.

Model/data Refresh – It refers to refreshing the dataset in the Power BI service with data from the original data source. This is done by either using scheduled refresh/refresh now. It requires access to on-premises data sources.

Tile Refresh – Tile refresh updates the cache for tile visuals on the dashboard once data alters. This happens every fifteen minutes. The user may also instigate a tile refresh by selecting the 3 dots in the upper right-hand corner of a dashboard and selecting Refresh dashboard tiles.

Visual Container Refresh – Refreshing the visual container revises the cached report graphics within a report once the data modified.

Q42. Is Power BI available on-premises?

Power BI is NOT accessible as a confidential core cloud service. However, with Power BI and Power BI Desktop, the user can securely connect to the users’ on-premises data sources. With the On-premises Data Gateway, the user can connect live to the other users’ on-premises SQL Server Analysis Services and other data sources. The user can also schedule refresh with a centralized gateway. If a gateway is not available, the user can refresh data from on-premises data sources using the Power BI Gateway – Personal.

Q43. What is data management gateway and Power BI personal gateway?

The gateway acts as a bridge between on-premises data sources and Azure cloud services.

Personal Gateway:

• Import Only, Power BI Service Only, No central monitoring/managing.
• Can only be used by one person (personal); can’t allow others to use this gateway.

On-Premises Gateway:

• Import and Direct Query supported.
• Multiple users of gateway for fabricating content.
• Central monitoring and control.

Q44. What is Power BI Q&A?

Power BI Q&A is a natural language tool that helps in querying the user data and get the results the user needs from it. The user attains this by typing into a dialog box on the Dashboard, in which the engine instantaneously generates an answer similar to in Power View. Q&A interprets the user questions and shows the user a restated query of what it is looking for from the user data. Q&A was developed by Server and Tools, Microsoft Research, and the Bing teams to give the user a complete feeling of truly exploring the user data.

Q45. What are how Excel can be experienced with Power BI?

Below are some of the ways through which we can influence Power BI:

The Power BI Publisher for Excel:

• May be used to attach Excel items (charts, ranges, pivot tables) to Power BI Service.
• May be used to link to datasets and reports stored in Power BI Service.
• Excel workbooks can be uploaded to Power BI and viewed in a browser like Excel Services.

Q46. What is a calculated column in Power BI and why would the user use them?

Calculated Columns are DAX expressions that are calculated during the model’s processing/refresh process for each row of the given column and can be used like any other column in the model.
Calculated columns are not compressed and thus require additional memory and result in decreased query performance. They can also decrease processing/refresh performance if applied on large amounts of data and can make a model more difficult to maintain/support given that the computed column is not present at the source.

Q47. How is data security implemented in Power BI?

• Power BI can employ Row Level Security roles to models.
• A DAX expression is applied on a table to filter its rows at query time.
• Dynamic security entails the use of USERNAME functions in security role definitions.
• A table is created in the model that connects users to specific features and roles.

Q48. What are many-to-many relationships and how can they be addressed in Power BI?

Many to Many relationships involves building a link between tables reflecting the combinations of two dimensions (e.g., doctors and patients).

• Two-way dual relationships can be used in PBIX.
• CROSSFILTER function can be used in Power Pivot like Excel.
• DAX can be used per metric to check and optionally revise the filter framework.

Q49. Describe Power BI Publisher for Excel?

• The user can use the Power BI publisher for Excel to attach ranges, pivot tables, and charts to Power BI.
• The user can manage the tiles by refreshing them or removing them in Excel.
• The Power BI Publisher for Excel can also be used to create a link from Excel to datasets that are hosted in the Power BI Service.
• An Excel pivot table is generated with a link to the data in Azure.
• The Publisher installs all the essential drivers on the local device to verify connectivity.

Q50. What are the differences between a Power BI Dataset, a Report, and a Dashboard?

• Dataset:
• The source is used to create reports and visuals/tiles.
• A data model is an Analysis Services Server
• Data could be inside of the model (imported) or a Direct Query connection to a source.
• Report:
• An individual Power BI Desktop file comprising of one or more report pages.
• It is built for profound, collaborative analysis experience for a given dataset.
• Each Report is linked to at least one dataset 
• Each page comprising one or more visuals or tiles is made available.
• Dashboard:
• a compilation of visuals from different reports.
• Built to average primary visuals and metrics from multiple datasets.

Q51. What are the three Edit Interactions options of a visual tile in Power BI Desktop? 

The 3 edit communication choices are  Filter, Highlight, and None.

• Filter: It completely penetrates a visual based on the filter selection of another visual.
• Highlight: It emphasizes only the related elements on the visual and shade out the non-related items.
• None