Interview Questions on Power BI – Vol 1

• by simple ms office
• 0 Comments
• October 26, 2022, 4:00 pm
• 815 views

Power BI has become a powerful business intelligence tool nowadays and many companies prefer this BI tool because of its visualization and performance.

And this is a Microsoft product so this tool is highly compatible with Microsoft Excel, Power Query, and SQL. So this series of questions and answers is dedicated to helping aspirants. And this will surely help you to increase your knowledge as well as your confidence level.

This is the first series of questions we have collected based on interviews in different companies.

Let’s start…

Q1. Which functions are used to override the inactive relationship with the active relationship?
Ans: USERELATIONSHIP()

Q2. Types of Filters in Power BI Reports?
Ans: There are four types of filters:

1. Page Lever Filter
2. Visual Level Filter
3. Report Level Filter
4. Drillthrough filter

Q3. Difference Between Distinct and Values?
Ans: Values() – If we applied the Values() function for a table then it will return all rows, if apply for the column then will return unique rows().
Measure1 = COUNTROWS(VALUES(Table1[columnName]))
Measure2 = COUNTROWS(VALUES(Table1))

Distinct()- Will return unique rows for both table and column.
Measure3 = COUNTROWS(DISTINCT(Table1[columnName])) 
Measure4 = COUNTROWS(DISTINCT(Table1))

Q4. What are Data Connectivity Modes?
Ans: 1. Import Query 2. Direct Query

Q5. What is DAX? Ans- Definition, uses, etc Q8. Full form of DAX? 
Ans: Data Analysis expressions.

Q6. Which language is used in Query Editor?
Ans: M Language

Q7. What is the full form of M?
Ans: M is the informal name of Power Query Formula Language, everyone calls it M! M stands for Data Mashup, and some say stands for Data Modeling.

Q8. Diff between SUM and SUMX?
Ans: SUMX is the sum of an expression, but SUM is just summarizing the values of one single column. SUMX is an Iterator Function

Q9. What are iterator functions?
Ans: Iterator functions are looping through all rows in the input table and storing the expression resulting in temporary memory storage. In the end, they apply the aggregation on the temporary storage results, release the memory usage, and visualize the calculation result.

Use of DirectQuery in Power BI Desktop
With Power BI Desktop, when you connect to your data source, it is always possible to import a copy of the data into the Power BI Desktop. For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.

When you use Get Data to connect to a data source supported by DirectQuery, the connection window lets you select how you want to connect.

The differences between selecting Import and DirectQuery are the following:
Import Query: 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. 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.

Benefits of using Direct Query
There are a few benefits to using DirectQuery
• DirectQuery lets you build visualizations over very large datasets, where it would otherwise be unfeasible to first import all of the data with pre-aggregation
• Underlying data changes can require a refresh of data, and for some reports, the need to display current data can require

Large data transfers, making re-importing data unfeasible. By contrast, DirectQuery reports always use current data
• The 1-GB dataset limitation does not apply to DirectQuery
Limitations of DirectQuery

There are currently a few limitations to using DirectQuery
• All tables must come from a single database unless using composite models
• If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor.
• Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross-filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model.
• Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, and so on) is not supported in DirectQuery mode.
• By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information.
• There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

Power BI Tips: Import vs Direct Query
Microsoft Power BI is a self-service Business Intelligence tool that lets you connect to multiple different data sources – Power BI offers 92 different data connectors to connect the data sources. With these connectors, you can connect to different data sources. Power BI offers two different Data Connectivity modes to connect the data sources – Import and DirectQuery.

How to choose the right Data Connectivity mode?
Both Data Connectivity modes are having their own unique capabilities and some limitations as well.

Import Connection: Import Data Connectivity mode lets you import data into the Power BI cache. Highly recommended to use an Import connection when the data size is less than 1 GB and the data is not continually changing. You can import data with schedule refresh to get the latest data. With Import connection, you can take full advantage of the high-performance query engine.

DirectQuery: DirectQuery Connectivity mode lets you connect directly to data. DirectQuery mode is used to build Real-time or near real-time BI solutions when the data is changing frequently, and the Data volume is very large. No data will be imported into Power BI. Instead Power BI will send queries to the data source upon building visuals/interacting with visuals. Each query is restricted to return less than or equal to 1 Million rows.

Here is the comparison between Import and DirectQuery

• interviews