PowerBI Interview Questions Set 03

41. Why we need BI?

We can take data and create the reports at the click of a button. Helps in attracting new customers to servicing and monitoring existing customers. We can keep Track of information and set our goals accordingly. In general, completely building an ETL solution]. (Extraction, Transformation, Loading) ultimately helping management to make better decisions. The efficiency of an Investment (ROI) is very high here. Making unwanted data into progressive information.

42. How to import the data in Power BI desktop?

This is the basic Power BI Interview Questions asked in an interview. Go to getting data Sources (where we find data sources from a different domain) and select the sources (Excel, CSV, SQL) then Load it. To see the data, click Data view. To choose the table click on the fields and you can pick a visualization to generate a report.

43. Give the difference between POWER BI vs TABLEAU?

Feature|POWER BI|TABLEAU

Data visualization|Focuses only on modeling and reporting.|Best tool Data handling|It drags down slow when handling huge data.|Handles bulk data. User Interface|Simple and easy to use.|Use a customized dashboard. Cost|Less expensive|Very expensive since it uses data warehousing. Machine Learning|It is associated with Microsoft Azure.|It has python Machine learning.

44. What are the Building blocks in Power BI?

The building blocks of Power BI are,

• Visualizations.
• Data Sets.
• Reports.
• Dashboard.
• Tiles.

45. Give the difference between Power BI Gateway and Data Management Gateway?

Power BI Gateway – It is software that works on premise Network (the data that are not stored in the cloud). It can be used for a single data or multiple data sources (SQL Server, SQL SERVER ANALYSIS SERVICES, other data sources, files, share point). The data are stored encrypted in the gateway-cloud service. Data Management Gateway – This component gets continuous data, expose tables and views.

46. What is the need of the new selection pane in the report Power BI desktop?

It is used to control the tab order between visuals on a page. We can combine two or more visual page into one visual group. It is used to select data in a visual for highlighting and drill down. Let us move to the next Power BI Interview Questions.

47. What data sets can be used to create a dashboard with streaming data tiles?

1. Streaming datasets (we need to have data which is cached in a memory before we use streaming data sets).
2. Hybrid Datasets.

48. What are DAX and the criteria to write DAX Expressions that references Calculated columns and measures?

This is the advanced Power BI Interview Questions asked in an interview. DAX is the language used by Power BI, abbreviated as Data Analysis Expressions. It is a functional Language, the execution flows with function calls. They are structured to work with tables. Example of DAX formula = SUM( FILTER( Values(‘Date[Year]’),))) Execution in DAX starts from inner function and works outside. It includes a variety of Time intelligence functions. There are two types of context:

• Row context – It applies to measures.
• Filter context – Applying filters to calculations.

When referencing a calculated column, we must include a table name and DAX code automatically has row context for the table in which calculated column is defined. When referencing a measure, we can optionally include a table name.

49. How Dynamic filtering in Power BI is performed?

Dynamic Filtering in Power BI is done by the following steps:

• Once all the data are set up publish detail report to Power BI.
• Publish it to Group Work Space.
• Create Filter Link (Ex: in url tab(? filter = Salesperson// Employee))
• Create DAX calculated Column.
• Public Overview Report

50. What are the parts of Microsoft self-service business intelligence solution?

Microsoft has two parts for Self-Service BI

Excel BI Toolkit

It allows users to create an interactive report by importing data from different sources and model data according to report requirement.

Power BI

It is the online solution that enables you to share the interactive reports and queries that you have created using the Excel BI Toolkit.

51. What is Power BI?

Power BI is a cloud-based data sharing environment. Once you have developed reports using Power Query, Power Pivot and Power View, you can share your insights with your colleagues. This is where Power BI enters the equation. Power BI, which technically is an aspect of SharePoint online, lets you load Excel workbooks into the cloud and share them with a chosen group of co-workers. Not only that, but your colleagues can interact with your reports to apply filters and slicers to highlight data. They are completed by Power BI, a simple way of sharing your analysis and insights from the Microsoft cloud. Power BI features allow you to:

• Share presentations and queries with your colleagues.
• Update your Excel file from data sources that can be on-site or in the cloud.
• Display the output on multiple devices. This includes PCs, tablets, and HTML 5-enabled mobile devices that use the Power BI app.
• Query your data using natural language processing (or Q&A, as it is known).

52. What data sources can Power BI connect to?

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

• Files: Data can be imported from Excel (.xlsx, xlxm), Power BI Desktop files (.pbix) and Comma Separated Value (.csv).
• Content Packs: It is a collection of related documents or files that are stored as a group. In Power BI, there are two types of content packs, firstly those from services providers like Google Analytics, Marketo or Salesforce and secondly those created and shared by other users in your organization.
• Connectors to databases and other datasets such as Azure SQL, Databaseand SQL, Server Analysis Services tabular data, etc.

53. What are the different types of filters in Power BI Reports?

Power BI provides variety of option to filter report, data and visualization. The following are the list of Filter types.

• Visual-level Filters: These filters work on only an individual visualization, reducing the amount of data that the visualization can see. Moreover, visual-level filters can filter both data and calculations.
• Page-level Filters: These filters work at the report-page level. Different pages in the same report can have different page-level filters.
• Report-level Filters: There filters work on the entire report, filtering all pages and visualizations included in the report.

We know that Power BI visual have interactions feature, which makes filtering a report a breeze. Visual interactions are useful, but they come with some limitations:

• The filter is not saved as part of the report. Whenever you open a report, you can begin to play with visual filters but there is no way to store the filter in the saved report.
• The filter is always visible. Sometimes you want a filter for the entire report, but you do not want any visual indication of the filter being applied.

54. What is DAX?

For basic calculation and data analysis on data in Power Pivot we can use DAX. It is a formula language to compute calculated columns and calculated fields.

• Pros and cons of Dax
• Dax works on multiple column values and Dax cannot modify or insert data
• We can calculate calculated column and measures, but we cannot calculate using rows.

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

• DAX works on column values.
• DAX cannot modify or insert data.
• We can create calculated column and measures with DAX  but we cannot calculate rows using DAX.

Sample DAX formula syntax: For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount] column in the Sales table.

A- Measure Name B- = – indicate beginning of formula C- DAX Function D- Parenthesis for Sum Function E- Referenced Table F- Referenced column name

55. What are the most common DAX Functions used?

Below are some of the most commonly 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

56. How is the FILTER function used?

The FILTER function returns a table with a filter condition applied for each of its source table rows. The FILTER function is rarely used in isolation, it’s generally used as a parameter to other functions such as CALCULATE.

• FILTER is an iterator and thus can negatively impact performance over large source tables.
• Complex filtering logic can be applied such as referencing a measure in a filter expression.
• FILTER(MyTable,[SalesMetric] > 500)

 57. What is special or unique about the CALCULATE and CALCULATETABLE functions?

These are the only functions that allow you modify filter context of measures or tables.

• Add to existing filter context of queries.
• Override filter context from queries.
• Remove existing filter context from queries.

Limitations:

• Filter parameters can only operate on a single column at a time.
• Filter parameters cannot reference a metric.

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

SUMMARIZE()

• Main groupby function in SSAS.
• Recommended practice is to specify table and group by columns but not metrics.You can use ADDCOLUMNS function.

SUMMARIZECOLUMNS

• New group by function for SSAS and Power BI Desktop; more efficient.
• Specify group by columns, table, and expressions.

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

Below are some of the benefits:

• By declaring and evaluating a variable, the variable can be reused multiple times in a DAX expression, thus avoiding additional queries of the source database.
• Variables can make DAX expressions more intuitive/logical to interpret.
• Variables are only scoped to their measure or query, they cannot be shared among measures, queries or be defined at the model level.

 60. How would you 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’),…….))