Power BI Dax

16). What is a dashboard

Ans: A PowerBI dashboard is a canvas which creates a story with templates and visualizations for better understanding of the data. It is a single-page report and contains the highlights of the data.

21). Why and how would you use a custom visual file

Ans: A custom visual file is used when none of the pre existing visuals fit the business needs. Custom visual files are generally created by Developers which can be used in the same way as prepackaged files.

53). Which language is used in Power Query

Ans: A new programming language is used in power query called M-Code. It is easy to use and similar to other languages. M-code is case-sensitive language.

31). What are the most common DAX Functions used

Ans: 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

40). What are the different Excel BI add-in

Ans: Below are the most important BI add-in to Excel:Power Query: It helps in finding, editing and loading external data. Power Pivot: Its mainly used for data modeling and analysis. Power View: It is used to design visual and interactively reports. Power Map: It helps to display insights on 3D Map. Power BI Interview Questions - Power Pivot

50). What are some common Power Query/Editor Transforms

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

38). What are some benefits of using Variables in DAX

Ans: DAX or Data Analysis Expression is a functional language which can create calculated columns and/or measures for smarter calculations to limit the data the dashboard has to fetch and visualise.

33). What are the purpose and benefits of using the DAX function

Ans: DAX or Data Analysis Expression is a functional language which can create calculated columns and/or measures for smarter calculations to limit the data the dashboard has to fetch and visualize.

28). Mention some advantages of Power BI

Ans: Few advantages of using Power BI are : PowerBI can input a huge quantity of data Information can be visualized using powerful templates and visualizations Users get cutting edge intelligence technologies and powerful algorithms that are updated regularly User can have personalised dashboards which are easy to access and understand Users can perform queries on reports using DAX language

57). What are the primary requirement for a table to be used in Power Map

Ans: For a data to be consumed in power map there should be location data like:Latitude/Longitude pairStreet, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by BingThe primary requirement for the table is that it contains unique rows. It must also contain location data, which can be in the form of a Latitude/Longitude pair, although this is not a requirement. You can use address fields instead, such as Street, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by Bing.

44). What are some of differences in data modeling between Power BI Desktop and Power Pivot for Excel

Ans: Here are some of the differences: Power BI Desktop supports bi-directional cross filtering relationships, security, calculated tables, and Direct Query options. Power Pivot for Excel has single direction (one to many) relationships, calculated columns only, and supports import mode only. Security roles cannot be defined in Power Pivot for Excel.

20). What are custom visuals in Power BI

Ans: In PowerBI you can create your own visualizations from the library of custom visualizations. A development project has to be created then test the visual in PowerBI service. Once the visualization is customized, it is thoroughly checked and tested before posting. After testing, the visualization is saved in .pbiviz file format before sharing. But you need to be a PowerBI Pro user in order to make custom visualizations.

19). What are the types of visualizations in Power BI

Ans: In PowerBI, we can represent the data in graphs and visualizations. The visualization can be of any type, for example:Bar and Column Charts: It is a standard visualization for looking at a specific value across various categories. Area Charts( Basic and Stacked ) : It is based on the line chart and the area under the line. It depicts the magnitude of change over time.Card: Card shows aggregate value of a certain datapoint, can be one or more but one per row.Doughnut and Pie Charts: They show the relation in parts of a whole. Doughnut charts have a hollow in the centre while pie charts don't.Maps: To show categorical and quantitative data with spatial locations.Matrix: It's a type of table with easier display that shows aggregated data Slicers: Slicer is used to filter other visuals on the page.There are other visuals like Combo Charts, Decomposition Tree, Funnel charts, Gauge charts, KPIs, Line Charts, Ribbon Chart, Scatter, Q&A, Tables, Treemaps, etc.

42). What is Power Pivot Data Model

Ans: It is a model that is made up of data types, tables, columns, and table relations. These data tables are typically constructed for holding data for a business entity.

45). Can we have more than one active relationship between two tables in data model of power pivot

Ans: No, we cannot have more than one active relationship between two tables. However, can have more than one relationship between two tables but there will be only one active relationship and many inactive relationships. The dotted lines are inactive and the continuous line is active. Find out our Power BI Training Course in Top Cities

18). What are the available formats

Ans: Power BI is available in different formats:Power BI desktop: You can download and install PowerBI Desktop on your personal computer, where you can connect to the data source, transform your data, analyze and visualize it with templates.Power BI services: It is a cloud based service or SaaS (software as a service). You can connect to data here as well but the modeling is limited. Power BI mobile app: One can securely access dashboards and reports on any device with the PowerBI app which is available for iOs, Android and even Windows.

56). What is Power Map

Ans: Power Map is an Excel add-in that provides you with a powerful set of tools to help you visualize and gain insight into large sets of data that have a geo-coded component. It can help you produce 3D visualizations by plotting upto a million data points in the form of column, heat, and bubble maps on top of a Bing map. If the data is time stamped, it can also produce interactive views that display, how the data changes over space and time.

41). What is Power Pivot

Ans: Power Pivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook. It lets you create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts. You can then further analyze the data so that you can make timely business decisions without requiring IT assistance.

54). Why do we need Power Query when Power Pivot can import data from mostly used sources

Ans: Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs and load it into Excel. It is most optimum to use Power Query over Power Pivot as it lets you not only load the data but also manipulate it as per the users needs while loading.

47). What is Power Query

Ans: Power query is a ETL Tool used to shape, clean and transform data using intuitive interfaces without having to use coding. It helps the user to:Import Data from wide range of sources from files, databases, big data, social media data, etc.Join and append data from multiple data sources. Shape data as per requirement by removing and adding data.

22). What are the various type of users who can use Power BI

Ans: PowerBI can be used by anyone for their requirements but there is a particular group of users who are more likely to use it: Report Consumers: They consume the reports based on a specific information they need Report Analyst: Report Analysts need detailed data for their analysis from the reports Self Service Data Analyst: They are more experienced business data users. They have an in-depth understanding of the data to work with. Basic Data Analyst: They can build their own datasets and are experienced in PowerBI Service. advanced Data Analyst: They know how to write SQL Queries and have hands-on experience on PowerBI. They have experience in Advanced PowerBI with DAX training and data modelling.

27). Which is a single-page canvas that uses visualizations to depict a story

Ans: PowerBI service dashboard is a single-page canvas that uses visualizations to depict a story.

49). What is query folding in Power Query

Ans: Query folding is when steps defined in Power Query/Query Editor are translated into SQL and executed by the source database rather than the client machine. It's important for processing performance and scalability, given limited resources on the client machine.

37). What is the common table function for grouping data

Ans: 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.

55). Name some commonly used tasks in the Query Editor

Ans: Some commonly used tasks in the Query Editor are:Connect to Data: Get Data from various sources and Transform data. Shape Data: Transform your data according to requirement to clean and shape it Group Rows: You can group the values of many rows into one single value by summarizing Pivot Columns: Pivot columns and create a table with aggregated values Create Custom Columns: You can use custom formulas to create new columns in your table Advanced Editor: You can make modifications to the data using Advanced Query Editor with query.

29). List out some drawbacks/limitations of using Power BI

Ans: Some disadvantages of PowerBI are: Complex in nature:One major drawback of PowerBI is it is designed in a complex manner. One needs complete knowledge of PowerBI in order to start working with PowerBI. Large data:PowerBI cannot handle large supply of data and might time out while processing a large data. PowerBI cannot process data more than 1 GB. Limited Sharing of Data: Reports can be shared only with users who have the same domain or have their emails listed in the Office 365. Limited data Source: Power BI can connect to real time data sets but there are very limited data sources that allow real-time connection to the PowerBI dashboards.Power BI DAX Interview Questions

35). What is the CALCULATE function in DAX

Ans: The CALCULATE function measures the sum of a column from any table and can be modified with Filters. Syntax: CALCULATE ( <Expression> [, <Filter> [, <Filter> [, ... ] ] ] ) Expression: The expression to be evaluated. Filter: A boolean (True/False) expression or a table expression that defines a filter.

34). How is the FILTER function used

Ans: 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)

26). How can you refresh data in PowerBI

Ans: The data can be refreshed in the Gateway in PowerBI by scheduling refresh.

25). Where do you reshape data in Power BI

Ans: The data can be reshaped in Data Editing of PowerBI.

17). What are the available views

Ans: The different views in PowerBI are: Report View : It is the default view which shows the visualization of the data in reports. You can create multiple report pages here with a wide range of templates and visualizations .Data View : Data view shows the transformed data in a table format with columns and rows. It also allows you to create new calculated columns for further insights. Model View : Also called, Relationship View, helps to create relationships between data models. All the models created in the data can be seen in this view and accordingly you can compare or create diagrams based on subsets of the model.

43). What is xVelocity in-memory analytics engine used in Power Pivot

Ans: The main engine behind power pivot is the xVelocity in-memory analytics engine. It can handle large amount of data because it stores data in columnar databases, and in memory analytics which results in faster processing of data as it loads all data to RAM memory.

23).What are the critical components of the Power BI toolkit

Ans: The most important components of PowerBI are: Power Query Power view Power Pivot Power Map Power Q&A Power Desktop Power Website PowerBI Mobile App

39). How would you create trailing X month metrics via DAX against a non-standard calendar

Ans: The solution will involve: CALCULATE function to control (take over) filter context of measures. ALL to remove existing filters on the date dimension. FILTER to identify which rows of the date dimension to use. Alternatively, CONTAINS may be used: LCULATE(FILTER(ALL('DATE'),.......))

48). What are the data destinations for Power Queries

Ans: There are two destinations for output we get from power query:Load to a table in a worksheet.Load to the Excel Data Model.

36). What is special or unique about the CALCULATE and CALCULATETABLE functions

Ans: 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.

32). What are the three fundamental concepts of DAX

Ans: Three fundamental concepts of DAX are: Syntax: Syntax is the formula which ncludes the functions. If a Syntax is incorrect, it will result in an error. Functions: Functions are arguments with specific orders to perform. It helps to calculate any particular order as required. Context: Context are of two types: Row Context and Filter Context. Row Context is used when a formula has a Function that applies a filter to identify a row in a table. Filter Context is used when one or more filters are used to get a value.

30). What is DAX

Ans: To do basic calculation and data analysis on data in power pivot, we use Data Analysis Expression (DAX). It is a formula language used to compute calculated column and calculated field. DAX works on column values DAX can not modify or insert data. We can create calculated column and measures with DAX but we can not 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. DAX - A- Measure Name B- = - indicate beginning of formula C- DAX Function D- Parenthesis for Sum Function E- Referenced Table F- Referenced column name

24). What is the maximum data limit per client for the free version of Power BI

Ans: With a Power BI Free licence a user can use 10 GB of storage in the cloud for hosting Power BI reports. The maximum size a Power BI report can be used in the cloud is 1GB.

51). Can SQL and Power Query/Query Editor be used together

Ans: Yes, a SQL statement can be defined as the source of a Power Query/M function for additional processing/logic. This would be a good practice to ensure that an efficient database query is passed to the source and avoid unnecessary processing and complexity by the client machine and M function.

52). What are query parameters and Power BI templates

Ans:Query parameters can be used to provide users of a local Power BI Desktop report with a prompt, to specify the values they're 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.

46). What is GetData in Power BI

With "Get Data" in PowerBI, you connect to different data sources to import data for analysis and visualization. You can select from a range of various data sources to import the desired data.Eg. Text/CSV, Excel, PDF, JSON, Amazon Redshift, SQL Server database, Access database, SAP HANA database, IBM, MySQL, Oracle database, Impala, Google BigQuery,etc.

15). What are the different types of filters in Power BI Reports

filters- Power BI Interview Questions - EdurekaAns: 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: These filters work on the entire report, filtering all pages and visualizations included in the report.We know that Power BI visual has an 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.