15 Power BI Interview Questions/Concepts

In this article I have summarized 15 questions and answers for Power BI that are important to know about if you are going for an interview related to Power BI.

Photo by LinkedIn Sales Solutions on Unsplash

You can also find the article on my web page eliasnordlinder.com, along with other Power BI aspects such as Resources, DAX-snippets and more.

1. Explain Direct Query vs Import, what are they, and when to use which one?

Direct Query

• You are querying data while running the report.
• Data is not imported into Power BI, but querying all the time to the data source
• Use Direct Query when want live data, and want to have a small size of the data model stored.
• Do not use Direct Query if want to have great performance with large data sets, or want to use a lot of Power Query in Power BI.

Import

• Data is imported and cached in Power BI as an Extract.
• Querying are done through the imported data source and not to the base data source.
• Use Import Data when you do not need live data and is okay with storing the data.
• Use Import data when want to have great performance with large data sets, be able to use Power Query, or combining multiple Data Sources.

2. Explain the Data View in Power BI Desktop

In the Data View in Power BI Desktop you can:

• Investigate which columns and data that you have in your different tables.
• Look at the data after is has been transformed in the query editor.
• Filter data and look at specific values
• Sort and format data
• Create new tables, formulas or measures

3. Explain the Model View in Power BI Desktop

In the Model View in Power BI Desktop you can:

• Create new relationships between different tables
• See a visual representation of all the relationships inside the model
• See which way the relationships are, and if one to many, one to one or many to many
• Add layouts to easier work with smaller parts of the model, if there is a big model
• Group different fields into folders inside the tables
• Hide fields, add names and add descriptions to different fields
• Format different fields

4. Explain the Report View in Power BI Desktop

In the report view in Power BI Desktop you can:

• Create the visualization/visual part of the reports, which means…
• Create report pages with different visual aspects such as charts, tables, Big Ass Numbers
• Format different aspects of the visualizations such as the labels, titles, legends etc.
• Create DAX measures
• See if a “column” is a calculated column, explicit measure, implicit measure or other.
• Add filters to the report

5. What is the DAX language?

What is DAX?

• DAX Stands for Data Analysis eXpression and is a functional programming language.
• It is used in tools such as Power BI, SQL Server Analysis Server and Power Pivot for Excel.
• The Syntax is very different from other programming languages, but mostly resemblances with SQL.
• According to Marco Russo and Alberto Ferrari “A matter of querying a set of tables joined by relationships and aggregated values”.
• The most dangerous thing with DAX is that you can write things that works, long before you understand what is happening behind the scenes.
• DAX Formulas can be written in Power BI, while DAX Queries only can be written in external tools, such as DAX Studio.
• The main building blocks of DAX are Calculated Columns, Calculated Tables and Measures.

Advanced concepts in DAX are

• Evaluation Context
• Filter Context
• Row Context
• Context Transition

Reasons to learn DAX are:

• If you are not utilizing DAX well, you are missing approximately 90% of the Analytical capabilities of Power BI
• You can evolve your Power BI Reports dramatically with DAX
• DAX is fun!, and the more you learn the more fun will you have on your journey
• You get more dynamic and can understand performance issues better by learning DAX.

6. What is the difference between a calculated column and a measure, when should you use which?

See my blog post about this for more information

What are Calculated Columns?

• Calculated Columns can be added in the “Data View” by pressing “New Column”.
• The column will be added and stored as an extra column in the chosen table.
• Calculated columns calculate values Row by Row (Important concept!)
• Calculated columns are useful in slicers.
• Calculated columns are only computed once, when they are created and never again -> This means that they take up precious space in the RAM memory!
• One reason to not use Calculated Columns, but measures is…..you guessed it….take up less RAM memory.
• If one can use calculated columns and measures for somethings, always use measures.

What are Measures?

• It is easiest to create measures from the Report Page through the formula bar.
• A measure is not stored in a table.
• Need DAX Studio or include in a visualization to see how it looks.
• Measure is not stored in RAM memory and is calculated at query time.
• Measures must be used when calculating ratios of aggregates, i.e percentages.
• Measures are difficult to understand at first, as they do not “Operate in Row Context” automatically.
• Always use measures if you can use both measures and calculated columns for something.

7. What are Aggregators and Iterators and how can you use each of them?

Aggregators

• Example: SUM(‘Sales’[Quantity])
• Functions that aggregate the values of a column in a table and returns a single value.
• They can work by only taking in one parameter.
• Examples of aggregators are: SUM, MAX, MIN, AVERAGE

Iterators

• Example: SUMX(‘Sales’,’Sales’[Quantity])
• Requires two parameters to work, in this case both the table and the expression
• The first parameter is always the table to “scan” and the second parameter most often an expression.
• Actually, all aggregators are iterators, but Power BI have utilized “Syntax Sugar” to write them in a shorter version.

Examples of iterators are: SUMX, MAXX, MINX, AVERAGEX

• Iterators and aggregators, such as SUMX and SUM, can be used interchangeable when the expression is a single column, but if you learn to use the Iterator here as well, it will come more natural to work with iterators in more advanced concepts where Iterators are a must.
• Besides Iterators and Aggregators, there are also “A-functions”, such as AverageA, but it is safest to not use them as the result of these are inconsequent.

8. What is Power Query, and how can you use it?

• Power Query is an engine inside Power BI, and Excel, that one can utilize to transform and prepare data.
• With the tool, Power Query Editor, one can use a graphical interface to perform these ETL-processes.
• One can also perform this programmatically with the help of the M-language.

Some of the things you can do in the Power Query Editor are:

• Connect to different data sources
• Combine different data sources
• Transform different data sources.
• Save the transformation, similar to a macro, to perform the same transformation again if the data is updated.
• Create functions to use on different data sources
• Utilize parameters to change connection for example between test and production servers.
• See the statistical distribution of the columns
• See the column profiles and column quality
• Create new columns, which can be done with formulas.such as index, bins and more.
• Change the data type of columns
• Pivot and Unpivot columns
• And muuuch more

9. Which are some of the most common DAX functions you use?

Some of the most common DAX functions that I use are:

SUMX: Sum a column or an expression, very useful iterator, Example:

• Total Sales := SUMX(‘Sales’, ’Sales’ [Quantity’] * ’Sales’ [Price]])

CALCULATE: Apply filters to Expressions and measures, Example:

• Swedish Sales := CALCULATE(Total Sales, Geography’[Country]=”Sweden”)

DATESYTD: Total dates up to last available date, used in Example (With Calculate):

• Sales YTD := CALCULATE ([Total Sales], DATESYTD (‘Date’[Date]))

DATEADD: Can manipulate to get dates in other periods, Example (With Calculate):

• Sales PY := CALCULATE([Total Sales],
  CALCULATETABLE(DATEADD(‘Date’[Date], -1, YEAR)
  ,’Date’[DateWithSales] = TRUE)))

10. How do you publish a report to Power BI Server?

You can publish a Power BI report to Power BI Server by:

• Open Power BI Desktop, and navigate to the report you want to publish.
• Sign in to an account associated with a Power BI Server in the top right corner.
• Go to the Report View in Power BI Desktop and click the button that say Publish
• You can publish in “My Workspace” or a workspace that you have created/have access to.
• You can press the link that comes up to go directly to Power BI Server and see the report.

11. What is, and how do you create an App in Power BI Server?

An App is similar to a workspace, but is only used to distribute reports from a specific workspace.

You can create and publish an Power BI App by:

• Go to a workspace in Power BI Server where you want to send reports to an App.
• Press “Include in App” besides the reports that should be included in the App.
• Click “Create App” to the upper right of the said workspace.

You will get a screen where you have to answer some setup questions about the app, such as

• App Name
• Description
• (Optional) logo
• Theme Color

You will get another screen to answer navigation questions such as:

• Order that reports will be shown in the App
• Possibility to Rename Reports in the Apps.

Finally you will get a third screen to answer permission questions about the app, such as:

• Who should have access to the app.
• What will the people that have access to the app have the possibility to do?(Connect to datasets, share app etc.

Share the app with your organisation by:

• Press the Get Apps Button below Apps in Power BI Server and search for the app that you (Someone in the organization) want to see.
• Click Get Now when they see the app (If they have access to see the app).

12. What is the difference between Power BI Pro, Power BI Premium per User and Power BI Premium?

If I would give a short summary of the list below, Power BI Pro can be used if you can manage model sizes < 1 GB, max 8 refresh per day/report, 10 GB storage per user, do not need paginated reports, deployment pipelines, data flows or XMLA endpoints.

You can do a lot with Power BI Pro and I think that a lot of small organizations use this.

Power BI Premium Per user is something of a middle ground between Power BI Pro and Power BI Premium, where you can still pay the per user price, but get some of the premium capabilities such as paginated reports and application life-cycle management.

Power BI Pro

• Price: 14 Dollar per User, per Month
• Mobile App Access and possibility to publish to Power BI Server
• No Paginated Reports
• 1 GB model limit
• 8 Refreshes/Day
• A lot of data sources, AI, API etc.
• Metrics for content creation
• No DataFlows
• No XMLA Endpoints
• 10 GB Storage per user

Power BI Premium Per User

• Price: 28 Dollar per User, per month
• Everything in Power BI Pro +
• Paginated Reports
• Advanced AI
• XMLA Endpoints
• DataFlows
• Application Life Cycle management
• 100 GB model size
• 48 refresh/day
• 100 TB storage/user

Power BI Premium

• 6858 Dollar per capacity/month
• Everything Power BI Premium per User +
• 400 GB Model Size
• Consume contents without per-user license
• On-premise report with Report Server
• Autoscale Add-on availability
• Multi-Geo deployment manager

13. What is the different between Implicit Measures, Quick Measures and Explicit Measures?

Implicit Measures

• Implicit Measures have the Sigma/Sum Icon in front of them.
• They are set by Power BI as summarized automatically and Power BI will always summarize these columns instead of individual values.
• You can change the implicit measure to Summarize as other ways than total sum, such as:
• Average, Minimum, Maximum, Count, Count(Distinct)
• But you can not do anything more than these basic calculations with Implicit Measures
• You cannot use any implicit measures when using Analyse in Excel in Pivot Tables

Explicit Measures

• Explicit measures are created with DAX code
• Example: SUM(‘Sales’[Amount])
• Explicit measures can are more complicated than implicit measures as you have to write them yourself.
• But they are much more flexible, as you can do other things than just the different ways of aggregation
• You can also reuse Explicit measure for example when calculating the margin
• You can use Explicit measures in Analyse in Excel.
• There is no difference in performance between Explicit and Implicit measures (If they are doing the same as sum of sales).
• Hide the column (Implicit measure) if you are creating an explicit measure with the same function
• I always move my, Explicit, Measures to a “Measure Group” (A Table with only Measures)
• I only use Explicit Measures

Quick Measures

• Quick Measures are Explicit Measures without having to write any DAX code yourself.
• I never use any quick measures as I think they tend to become quite confusing.
• But they are very quick to create and they are built on DAX code which mean that they have the pros that explicit measures has.

14. What are the different kind of filters you can use in Power BI?

Automatic Filters

• Filters automatically added when building a visual.

Manual Filters

• Drag and drop to the filter pane while editing a report

Include/Exclude Filters

• Right click and press include/exclude on a visualization and they will automatically get added to the filter pane.

Drilldown filters

• If you have the drill-down functionality and press on the dimension you want to drill down, these filters are automatically added to the filter pane.

Cross-Drill filters

• Drill down filter but connected to another visualization, will be added to the filter pane.

Drillthrough filters

• Drill down filter but connected to another page, shown up in the “Drillthrough pane”.

URL filters

• Add an URL query parameter.

Pass-through filters

• Filter created through Q&A.

15. What are deployment pipelines?

Deployment pipelines is a life cycle management tool in side Power BI.

• To utilize Deployment pipelines one need to have at least Power BI Premium/User
• Deployment pipelines are a tool inside Power BI Server
• Deployment pipelines divide the reports in the three different categories:
• Development (Build up the reports and upload them here)
• Test (Share content with other people to test, increase amount of data in the models)
• Production (Move here when the final report is ready)

Conclusion

In this blog post I have gone through 15 interview questions/concepts with answers which I think demonstrate your knowledge of Power BI.

Questions or inquiries

If you have any questions or input please contact me on

LinkedIn: www.linkedin.com/in/elias-nordlinder
Email: Elias.Nordlinder@gmail.com
Webpage: eliasnordlinder.com

Have a great week everyone
//Elias