Power BI Interview Questions - DAX

Welcome back to another edition of our Tech Interview series! Whether you are going on an interview for a Power BI role or you’re the one conducting the interview we’ll cover questions, best answers and discuss key points to get you prepared. This edition focuses on DAX questions you may ask or be asked in an interview.

We’ll start with some basic DAX questions and move to a few more advanced concepts. Be sure to watch the included video for demos of what we discuss.

1. What is DAX?

Answer: DAX stands for Data Analysis Expression language. DAX is the ability to extend your data by using calculations and aggregating it. It works on functions, operators and constants and it pulls it together to further extend your model. Let’s say you wanted to calculate year to date sales or compare profit year over year, any point of complexity is a great use case for DAX to aggregate and pull things together.

KEY POINTS:

• DAX helps you create/extend new information from existing data already in your model
• DAX formulas will help you get the most out of your data
• Used in Power BI, Power Pivot for Excel and Analysis Service Tabular

2. In our last episode on Data Shaping interview questions, we talked about the Query Editor and using Power Query to build on what we have, and one thing we can do is add a new column. When would I decide whether to do something in the Power Query Editor vs DAX to create a column?

Answer: It depends on what you want to do. You want to use the right tool for the job so if you’re focusing on transformation things that are done upstream, like ETL work, you’ll want to use the Power Query Editor to benefit from adding a column as a transformative element.  

You’d want to use DAX for calculations, analytics, aggregation work a bit further downstream. (For a demo in the desktop where you would use a DAX column vs a Power Query column, be sure to take a look at the video.)

KEY POINTS:

• Use the right tool for the job
• DAX (focuses on Power Pivot) is for building analytic purposes
• Power Query, or M, is for data shaping/transforms as the data comes in

3. Why would I choose calculated columns vs calculated measures inside of Power BI?

Answer: A calculated measure is a higher-level DAX measure that’s used over the entire model. A calculated column is focused row by row. Let me show you:

• I start in Power BI Desktop in the Data View under the Modeling tab. This is where I want to add a new measure or a new column. Doing calculated columns in the Data View instead of the visual layer allows you to see the results.
• Under Internet Sales I’ve already created two examples. I have a profit margin column and you can see in my demo that this column is iterating row by row, so it’s giving me a value row by row and I can scroll far down and I can see it change over time but it’s not a good way to display this information.
• When looking for profit margin, I want to see it at a high level and see it aggregated over the entire model.
• I also have a measure which I can add by clicking New Measure at the top ribbon and I’m using the SUM function because I’m aggregating over the entire table rather than row by row.
• You can see in our demo that when you’re aggregating across the entire model, you’ll want to use a measure since a column will give you a false profit. Add a column when you want to use row by row operations.

KEY POINTS:

• Columns:
  • Use when working with categorical data
  • Computes values for each row during a data refresh
  • Does not depend on user interaction
• Measures
  • Use when aggregating a value
  • Used for aggregating values from many rows in a table
  • Results may change based on user interaction in report
  • Works across the entire model

4. The next question is regarding time intelligence in DAX. In DAX we can do year to date and parallel period and such, which is great for financial reporting. What are some prerequisites for doing time intelligence?

Answer: The main prerequisite is you need a date table, more specifically, a continuous date table with a whole range of dates (no breaks in the dates). That way you can calculate over that continuous line. You can build that date table using the M language in the Power Query Editor, use DAX or if you already have one in your model or data source you can bring it into Power BI.

The second element you need is to define the relationship with the date table and the other appropriate tables.

Remember all dates should be present without exception and you need a proper relationship for time intelligence to function the way you intend it to.

5. What is filter context and how does it impact the way I work with my data?

Answer: Filter context is important because it affects everything. It is all the influencing filters that will affect how your data displays itself; at the visual level, report level, relationships and DAX calculations impact the filter context, basically anything that filters and slices up your data and how you’ll see it. Let’s see how this works:

• So, I have a slicer in my example. If I were to select a value in that slicer, it will impact the filter context. In my case, depending on what year I choose in my slicer, my data is filtered, and it affects what I see visually.
• Conversely, in the Filter Pane, I have a filter applied so that all the finished goods that are flagged for true are going to show up and if it’s false they will not. I also have one for English Product Name so I’m not seeing other language names. These all affect the filter context.
• When I go to the model view, there is filter context based on our relationships. I can look at the lines and arrows between my relationships and I can see how this is being filtered table to table based on the relationship. The arrow shows how the relationship is filtered into the other table, whether one way or bi-directionally.
• In talking about DAX and filter context, is there a way to manipulate that filter context in any way or override it?
  • One way to override the filter context is to use the Calculate function. I pull US Profit Margin over to Values and when I highlight it, you can see the Calculate function. Calculate is an overriding function of filter context which means if I use the Calculate function, anything that’s created from that is not going to be filtered the same way as the other visuals would be.
  • This is one way to keep the data constant and can be helpful to compare it from a different angle using that Calculate function and compare/contrast it with another column that has a different filter context.

KEY POINTS:

• Filter context goes into all the things within your report that affect how your data is filtered down. This could be determined by multiple sources such as:
  • Your visual is impacting filter context by adding a slicer.
  • Your filter area of the report is affecting filter context.
  • Your DAX calculations could impact it, like our Calculate function example used to override filter context.
  • Relationships are a critical element of filter context

That wraps us this edition of Interview Questions for DAX; we hope you found it helpful in preparing to impress on an interview for a DAX position or if you’re the one asking the questions of a candidate.

Please let us know in the comments below if you have any questions you’d like to see answered or for future topic for us to cover in our Interview series. If you’re looking for training to prepare you for a new role or step in your career, our On-Demand Learning platform has 15+ Power BI courses as part of over 50 deep dive courses covering Azure, Analytics, SQL Server and much more, that will give you the training you need. Get started today with our FREE Dashboard in a Day course with 7+ hours of Power BI content— just click the link below!