Power BI Interview Questions and Answers

Incremental refresh feature is released  in May 2018, it is traditionally reserved for large models (Like more than 1 GB) in analysis servers for high-end scalability of data by publishing only to workspace in Power BI Service where Premium capacity is enabled for them.

Eg: It will increase the speed and efficiency of data refresh by just refreshing the data changes in last 1 day -10 days instead of loading last 3 years of data.

To enable it just right click on tables in fields pane and enable Incremental refresh. 

One of the most frequently posed Power BI scenario based interview questions, be ready for it.  

This can be achieved by Dynamic Row Level Security(RLS) in Power BI.

Steps to be followed:

• In Modeling menu;
• Select Manage Roles.
• Create & define Role name.
• Now, select the required table and UserPricnicpalName() function and publish this to Power BI Service.

Once users log in to Power BI service with their respective user id (UserPricnicpalName()), they will be able to see only data based on the role we created.

To attain this need to create Content Pack,

• Need to select Specific Group of people (User Email ids or Distribution lists) (OR)
• My Entire Organization in then we choose give access to Dashboards or Reports or Datasets.

A staple in BI interview questions, be prepared to answer this one.  

This major feature was introduced in July 2018 called Composite Model. Here, Power BI desktop author is empowered to combine the data, which is coming from a variety of sources, and establish relationships between those tables in data model.

• DirectQuery, which is a large table in SQL server and doesn't need to download in data model.
• Dual, which is a small table in SQL server, can be downloaded easily into data model.
  However, this will be only in RAM and will not be saved in .PBIX.
  To enable this feature, right clicking on the table in the Fields pane and select table properties, another window Field properties pane will appear and can change Storage Mode from DirectQuery to Dual
• Import, will import the data from sources like CSV or Excel or Azure SQL server or Azure Data Lake or Azure Cosmos to data model and will occupy the space in .PBIX

This question is a regular feature in Power BI interview questions, be ready to tackle it. 

• Power BI Dashboards are available in Power BI web Service.
• You can use the DrillThrough page, Bookmarks, and Selection pane.
• Tiles can be directly pinned from reports to dashboards which gives a very high-level day-to-day view of the main KPIs of business, and by a click on tiles will navigate back to reports for a detailed view.
• The report layout can be set to Mobile view.
• Apart from that, various tiles can be created like Web Content, Images, Textbox, and integrated URLs, Videos, and custom streaming data tiles.

• Power BI reports are created in Power BI Desktop.
• Advanced Q&A and tooltip option can be enabled.
• Various report page size can be set like (16:8, 4:3, Letter, Tool Tip, Cortana, and custom).
• Set Page Background and Wallpaper.
• Can use DrillThrough page, Bookmarks and Selection pane.
• Download Custom Visualizations from marketplace
• Set Color Themes to reports
• Can printout of reports similar to Microsoft PowerPoint Presentations.
• Exported to PDF
• Contains 190+ DAX functions to apply on our data, whenever necessary.
• Save as Template file (.PBIT).
• Report layout can be set to Desktop as well as Mobile.

Yes, it’s possible by following the steps below:

• Firstly, Power BI Admin portal, in tenant settings “Analyze in Excel” option must be enabled by Power BI Administrator.
• Then at upper right corner, click settings to download and install Excel updates.
• Next left-hand side navigation pane, got to workspace > dataset > click on Eclipses (three dots) and select Analyze in Excel.
• Download .ODC, save and double click on it.
• Post that need to provide power BI user id and password.
• Which will connect to dataset in Power BI.

• Data Stories Gallery, is a place where all the designers of Power BI across the different countries, those who have analyzed the data out of their personal interest and brought the data back to life and wanted to showcase the rest of the world about their outstanding skills.
• They upload Power BI (.PBIX) with their Storytelling and anyone can download their work.

Steps to go to Data Stories Gallery:

• Open PowerBI.com in favorite browser.
• By hovering on Learn
• Click on Community
• Scroll down a bit and you will find Data Stories Gallery.
• Also find Power BI reports where they have designed games.
• And anyone can submit her or his Data Story as well.

• Here any Power BI report designers or consumers or developers or admins can suggest ideas(that could be anything new feature, or enhancement etc.) which can improve user Experience and currently that is what Microsoft is listening to User Voice”.
• Once this is posted in ideas page, then different users across the world, possible needs similar or same kind requirements and they start supporting idea by Voting it.
• No of votes increases then your idea has got demand which is called “Hot Idea”.
• Then Microsoft product team will consider it and releases that feature in subsequent releases.

It's no surprise that this one pops up often in Power BI interview questions on DAX.  

DAX is the language of Power BI. It stands for Data Analysis Expressions. It is a functional language, much like Excel formulas.

Power BI Product team created DAX by mashing up “Excel formula Language”, “SQL Language” and “MDX Language.” 

DAX is Simple, but it’s not easy. Do not try to learn It in the usual way. You need to spend time understanding the hard parts. 

E.g.

Syntax elements:

1. Name of Measure is “Total Sales”.
2. operator (=) equals sign implies its beginning of the formula and returns the result.
3. Adds up all of the numbers in the column Sales[SalesAmount] using SUM Function
4. All functions require one or more arguments surrounded by Parenthesis ()
5. Name of Table “Sales” 
6. The column [SalesAmount] in the Sales table and SUM function is used to aggregate Sales.

It’s very important to understand the Logical Processing & execution in DAX and also to format using Pressing (Shift+Enter) to moving it to next line and indented with spaces, unlike excel functions to write in 1 line mixing all the formulas in the formula bar.

Explanation - DAX Logical Processing & execution starts from the innermost function i.e., IF() function – which is checking condition Country is equal to India by selecting distinct or unique values using VALUES() Function, parameters which is first part executes, then moves Filter() function to filter it by Country Function then finally moves Outermost SUMX Function to aggregate SalesAmount and multiply by 20% if its country is India and else with 10%.

There are two types

• Numeric types:
  • Whole Number (Integer (64 Bit)) - A 64-bit (eight-bytes) integer value
  • Decimal (Floating points) - A 64-bit (eight-bytes) real number
  • Currency (Money) - Currency data type allows values with four decimal digits of fixed precision.
  • Date (Date/Time) - Dates and times in an accepted date-time representation.
  • Date – Represents just a Date (no time portion). 
  • Time – Represents just Time (no Date portion). 
  • Date/Time/TimeZone – Represents a UTC Date/Time. 
  • Duration – Represents a length of time.
  • Boolean - Either TRUE (1) (OR) FALSE (0).
• Other types:
  • Text (String) - A Unicode character data string. Can be strings, numbers or dates represented in a text format.
  • Binary Object – Images or any kind of binary large objects.
  • N/A or Blank - A blank is a data type in DAX that represents and replaces SQL nulls. You can use the BLANK function, and test for blanks by using the logical function, ISBLANK.

Four types of operators are available in DAX Language to perform calculations:

To compare values then use Comparison Operators.

To perform arithmetic calculations on values, use Arithmetic Operators.

To join or concatenate two or more Text values then Concatenation operators.

To return Single values based on True or False, use Logical operators.

A common question in Power BI interview questions, don't miss this one.  

To create DAX formulas, one should understand Context. Context is nothing but the result of the DAX formula will change based on the Current Cell or Row for any related data.

Types of Context:

• Row Context (Multiple Row Context):

Current Row or a single line of record is called a Row context.
E.g. Table “Sales”

Here we must create Measure to derive the Total Sales i.e. Row by Row.

Hence we have to use SUMX() Function to calculate the Price X Quantity.

In SUMX ‘X’ mean Iterate. Row by Row data calculate/Evaluation

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

• Working Logic: 

First, go to Row 1 =Take Product of Price and Quantity as 3 * 1

Move to next Row 2= from row 1 result ‘3’, sum up with Product of Price and Quantity as 2*2 And so, on till it reaches the end of the table.

• Working in the backend:

Row 1 = 3*1

Row 2= 3+(2*2)

Row 3 = 7+(1*3)

Total Sales = 10

Example Snippet for better understanding:

• Query Context:

As we all know Context will change based on the Current Cell or Row for any related data.

For example, consider Snippet below, you pull the Net Sales into Table or Matrix visual (Basically Tabular model), then according to Row and columns are chosen from Dimension table, Slicers selected or any kind of report filters applied Data will appear.

• Filter context:

Filter context is simply applying a filter on evaluation while creating a measure.

E.g. Below used Calculate() function, which has the capability to change the context by applying filters.

Basically its evaluation Profit for Same Period Last Year using SamePeriodLastYear() function in Calculate.

One can create DAX Calculations or queries using formulas which can be used based on need basis and called them as

• Measures,
• Calculated Columns,
• Calculated tables,
• Row Filters (RLS - Dynamic Row-level Security).

Measures (OR Calculations OR Calculated Measures):

• Measures are calculations which are created logically (RAM) and are faster than calculated columns, which occupy the space in Power BI Desktop file (PBIX).
• Will not occupies space unless dragged and dropped in Visuals.
• These are created in DAX formula bar in Power BI Desktop.
• It is displayed with Calculator Icon
• Can also create & maintain individual “Key Measures” table and also folders in the to segregate like in below E.g.

• We must use functions like Count, Sum, Min, Max, etc. to call any column for creating measures.

E.g. Net Sales = SUMX(Sales, Sales[Quantity]*Sales[Price])

• We cannot use measures in Slicer as contains value and will change depending on Context.
• In the model, measures can also hide & unhide on need basis.

Calculated Columns:

• Calculated Columns are created physically and are slower than calculated measures.
• These are created in DAX formula bar in Power BI Desktop.
• We can refer any column directly to create new column or derive column by using functions like Lookup(), concatenate(), format(),Sum() etc.…
• Below is example for creating column

E.g. Sales_Column = Sales_[Costs]*Sales_[Qty]

• We can use Calculated Columns in Slicer.
• Also, can create relationships between two or more tables.
• On a need basis, columns can hide & unhide in the model.

Calculated Tables:

• Calculated Tables can be created like Date Dimension (DimDate) or it can be derived based on existing tables in the model.
• These are created in DAX formula bar in Power BI Desktop.
• It also supports to establish relationships between other related tables.
• These can be hidden or unhide based on the requirement.
• Below is an example for creating Calendar table using DAX

E.g. DimDate = CALENDAR("01/01/2019","03/31/2019")

Row Filters (RLS – Dynamic Row-level Security):

• Row Filters also called as Dynamic Row-level Security.
• This is to filter the records based on Hierarchy.

e.g. You are General Manager of Sales for India and under you have Associate Managers which looks after sales different states in India. So, the requirement is your Associate Managers should be able to see only respective states sales data (not others).

This requirement can be achieved by RLS and need to use a username () – for Names or userprincipalname () – for official Email ids DAX functions.

Variables are used to store values which allocate in Memory and in future which can be used in any kind of calculation.

By using variables, we can simplify large (2-10 lines of) complex DAX formula into simple chunks which helps anyone easy to read and understand easily.

Variables help in prevent writing of the same expression again and again.

Syntax:

VAR <Variable_Name> = Value or Condition
RETRUN
<Expression>

E.g. 

• To create calculated column where salary greater than 10,000 then Display as High,
• salary greater or equals to than 5,000 and less than or equals to 10,000 then Display as Average,
• salary less than 5,000 then Display as Low,

Let’s take DAX formula as complex form without variables: 

Salary status = 
if (EMP[Salary] >10000, "High", 
        if(EMP[Salary] >=5000 || EMP[Salary] <=1000, "Average",
            if(EMP[Salary]<=5000, "Low")
           )

Let’s take DAX formula using variables in chunks: 

Salary status = 
Var High = EMP[Salary]>10000
Var Avg = EMP[Salary]>=5000 || EMP[Salary]<=1000
Var Low = ‘Emp’[Salary]<=5000
Return
If (High, "High",
if(Avg,”Average”,
if(Low,”Low”)
    )
)

The output will be the same for both without and with Variables, however with Variables easy to read and understand the DAX code.