Recently, I interviewed a few people for Power BI roles and here are some questions I asked in the initial rounds to assess their skill level. I’ve included sample answers below so you know how to approach such questions.
Prefer video? See this...
I made a video with 10 questions and my (elaborate) answers to them. Watch to learn how to answer such interview questions. See it below or on my YouTube channel.
1. What are the main differences between Power BI Desktop and Power BI Service?
Answer cues:
- Power BI Desktop is the main tool for creating or authoring reports.
- We use PBI desktop to clean up data, create data model, set up measures, build and refine charts and construct the reports.
- We then use Power BI Service to share the reports with the audience.
- Desktop = creation tool, Service = consumption tool
2. Explain the difference between a measure and a calculated column?
- Measures:
- calculate things on top of tables.
- calculated in the run-time based on the evaluation context.
- are usually aggregates.
- example: Shipment Count = COUNTROWS(Shipments)
- Calculated columns:
- are part of the table.
- are calculated at the time of creation based on the row-context.
- have one value per row.
- example: Discount Rate Row-wise calculation of discount rate based on order quantity
- Both measures and calculated columns use the same DAX language.
3. Describe the steps you would take to connect multiple data sources in a Power BI report?
- We can use Power Query to connect more than one source of data.
- We can also use PQ to merge / append / combine the data as needed.
- We can selectively load the data to Power BI and keep the rest in PQ level but customizing the load behaviour.
4. What do 1, * and arrow (▶) mean in the data model diagram?
- In a typical star-schema, we will have one to many relationship between dimension and fact table.
- The one side is denoted with a 1 and many side is denoted with a *
- The arrow indicates the direction of filter propagation.
- Power BI allows us to customize the filter direction (uni or bi-directional) and also have many-to-many relationships.
5. What is DAX and give me an example of DAX you've recently used?
- DAX stands for Data Analysis eXpressions. It is the main language of Power Pivot.
- Give examples based on your experiences and tell why / how they helped you solve problems.
6. How do you optimize the performance of a slow Power BI report?
- Answer questions like this based on your experience. If you have never optimized something, be honest and say that. Then give theoretical answers.
- Key optimization startegies:
- Reducing the data (filtering at PQ, Source level)
- Removing unwanted visuals, interactions and bookmarks
- Using performance profiler (Optimize ribbon in Power BI) to measure the performance of a page and identifying the problem areas.
- Setting up aggregate tables, pre-calculated views.
- [NEW] Using visual calculations instead of measures.
- Upgrading the Power BI on-prem servers, database servers (for direct query)
- Running time-consuming operations (such as PQ transformations) at low usage times (midnights, evenings)
7. How do you publish a Power BI Report to the service?
- Save and test the report. Make sure all calculations are correct and visuals represent the truth.
- Test any connections, refresh processes too.
- Publish the report (Publish button) and set the correct workspace.
- Test the report on the service view and make sure right people have access to the report.
- Optional, Send an email or share the report with the audience.
8. Explain RLS (Row Level Security) in Power BI?
- Row Level Security allows us to provide access to the relevant data to right people.
- For example, we can use RLS to show only USA data to the USA regional manager.
- We can use “roles” option in Power BI to set and test the roles.
9. What is a Slowly Changing Dimension (SCD) and how do you handle it in Power BI?
- SCD (Slowly Changing Dimension) is a dimension (or aspect of a dimension) that changes slowly over time.
- Give examples from your industry or previous work.
- Example: In our product table, we have a feature called cocoa percentage. For certain products this is changed once in a while based on customer feedback. This is an example of SCD.
- We can either replace the old values with new ones or create effective date based records.
10. How would you handle missing data?
- We can use either Power Query or Power Pivot to handle missing values in our data.
- Power Query shows missing values in a column thru Column Quality feature. We can use this to identify and deal with missing values.
- The key strategies for dealing with missing data are:
- Removing missing values
- Going back to source and fixing the problem
- Replacing missing values with an approximation (imputation)
- Ignoring missing values
Need more help? Watch the video
I made a video with 10 questions and my (elaborate) answers to them. Watch to learn how to answer such interview questions. See it below or on my YouTube channel.
Learn more: Power BI Weekend (2023)
My annual Power BI event – Power BI Weekend is happening this year on November 18 & 19. In this 4 hour event (2 hours on Saturday – Nov 18 & 2 more on Sunday – Nov 19), you will learn the Power BI Essentials to say yes to your next challenge.
The topics covered are,
- What is Power BI? How to use it?
- Power BI vs Power Query vs. Power Pivot
- Understanding Power BI Visual interactions & customizing them
- Data cleanup and transformations with PQ
- Setting up a star schema data model
- Creating and using DAX measures
- Interactive storytelling in Power BI
- Saving & publishing your work
- Resources to learn more
- Q&A with you
Tickets are on sale now. Book yours before they sell-out.
27 Responses to “9 Box grid for talent mapping – HR for Excel – Template & Explanation”
Great stuff! I can understand how to add a slicer to the pivot table, but how do you implement the departmental selector on the 'Filter' formula scheme?
Just saw this on your Youtube channel, and it’s areat idea...!
An easy way to overcome the "ugliness" of pivot tables and get it to look nice (in the format of the Output sheet), would be to simply build a sheet with the nice map at the top, a pivot underneath it and a slicer next to formatted map and then reference each of the 9 cells in the formatted map to the “related” cell in the Pivot.
Keep up the good work!
/Claus
Thanks Claus. That is a great idea 🙂
Hi Chandoo,
This is great! Curious how to make additional columns operate the same as the Department column (ex. have a "manager column") that would allow you to sort a 9 box by manager, area, or team in addition to department?
Feel free to email me if needed! mfry01@minnetronixmedical.com
Happy New Year
Madison Fry
I am curious about the smae thing. I would like to populate the 9 box with other views as well by adding additional columns. IE., I would like to add location, region, etc. Thank you.
This is great, thank you!
How can i see the whole data set of all the teams in the output table. Need a formula that will pick up all the employees
Hello,
Love the template. Thank you. Question - the drop down to pick a department on the Output tab does not seem to work on the downloadable template. Am I doing something incorrectly?
Thank you!
Hi Heather... Thank you. I am using Excel 365 to make the calculations. If you are using an older version of Excel, then the drop-down filter won't work.
Hi
I was able to follow your 9 box grid and modified based my needs. However, you tutorial did not show how to you create the filter for the "Pick a department. Can you kindly share how to create that filter that updated the grid. Thank you.
I am working on this project but I am struggling with the data validation for the department. I copy the worksheets data entry and output as the managers want to see different tabs for each managers.
I updated the source reference for each tab but It does not update the grid based on the new source. The list was updated but it does not populate the grid based on the performance and potential listed.
In addition the hyperlink Update Data and View Talent Map no longer works. Can you please help me.
I keep getting this error message in the pivot table:
This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 14, ‘.
Hi, I used your 9-box excel template with excel 365. First off, thank you so very much. It is incredibly helpful!! My only question is that the boxes aren't big enough for all of the employees (specifically the middle which we call 'Core Employee'). Is there a way to make the boxes larger? Even though it is in excel, I am not able to increase row height (like I normally do in a speadsheet). Any ideas? Thanks again, Jody
Hi Chandoo,
Thanks for the great content. Re. 9 box grid, pls advise how do I increase the size of the box to accommodate more names?
Hi Chandoo,
I figured it out. Excel 365 has the format row height on the ribbon. Thank you
Merci Chandoo pour le modèle proposé,
j'ai une question et un souhait est il possible de développer davantage ce modèle en insérant la photo de chaque employé.
Hi Chandoo!
Great tutorial and tool, thank you! Your tutorial didn't include how to create additional filters on the "Output" tab. Could you please share how you did it?
Can this be done exactly in google sheets?
Hi Chandoo,
Thanks for the video it was really helpful. Is there any way to multi select the dropdown to display multiple or all departments rather than just one at once?
Hello Prish
I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is?
Many thanks
Hello Jody, I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is? Many thanks
Is there a way to change the 9 box wording descriptions, i.e. Work Horses, to our own internal langauge?
You can edit the file. The descriptions are textboxes.
Hi Chandoo, this is awesome and has worked perfectly. Due to a big organisation the 9 box grid on the output file is too small. I tried adjusting using the row/width ribbon under the format ribbon however it doesn't seem to work. Is there an easier way to adjust this?
Thanks!
When I drag the formula, it doesn't work, and the order I use with the data changes. In the beginning, the order is it is " candidates," " potential," and " performance," but when it goes to another column, it is " Potential," Performance," and "Candidates."Can you help me? Thank you very much, sending love from vietnam
Hi- I am working on the 9 grid project and I am trying to expand the box since I have over 100 names on a few of the columns. How do I do that?
Hi, Thank you this is great stuff and really useful.
As well as department as demonstrated on your clip, how can I display all candidates on the grid at once?
Many thanks in advance