Top 100+ Power BI Interview Questions- Part 1
In today’s data-driven world, the demand for effective data analysis and visualization tools has skyrocketed, and Power BI has emerged as a leading solution in the realm of business intelligence. As organizations seek to make data-informed decisions, Power BI’s versatility and user-friendly interface have made it a top choice for professionals across various industries.
For individuals preparing for a Power BI interview, demonstrating a strong grasp of the tool’s functionalities and data manipulation capabilities is essential to stand out from the competition. In this comprehensive blog post, we will delve into the most commonly asked Power BI interview questions and provide insightful answers to help you excel in your interview.
As Power BI interviewers, we value both functional knowledge and strong technical expertise. We aim to ensure that candidates not only understand the concepts but have practical experience working with Power BI. To assess your hands-on experience, we may tweak questions to check the application of concepts.
For instance, we might present a scenario to gauge your practical skills instead of asking a basic question about interactions. For example, we could ask:
“In a Power BI report, you have a page that contains two slicers say- Slicer 1 and Slicer 2. Additionally, there are two Line visuals assuming Line Visual 1 and Line Visual 2 are on the same page. How would you make sure that Slicer 1 only filter Line Visual 1, and Slicer 2 only filter Line Visual 2?”
By presenting such real-world scenarios, we aim to assess your ability to apply Power BI concepts in practical situations. Your response to this question will demonstrate your technical understanding of setting up interactions and will reveal your familiarity with the Power BI interface and functionalities.
Throughout the interview, we may present similar scenarios and technical challenges to further evaluate your expertise. This approach enables us to determine your level of proficiency in Power BI. It ensures that we select candidates who not only possess theoretical knowledge but can also effectively translate that knowledge into real-life projects and reporting tasks.
Organizations encourage you to come prepared with hands-on experience and be ready to showcase your practical skills. Demonstrating your ability to work with Power BI confidently will significantly enhance your chances of excelling in the interview process and becoming a valuable addition to the team.
To prepare for the technical portion of the interview, I highly recommend taking advantage of the 11-hour video course provided. This course will offer a comprehensive understanding of the Power BI concepts and features, allowing you to build a solid foundation for answering technical questions during the interview.
Additionally, I advise you to actively participate in the Power BI community and attempt to solve questions posted by others. Engaging in community discussions and problem-solving exercises will help sharpen your technical skills and deepen your knowledge of Power BI.
For a more advanced understanding of Power BI, I encourage you to watch the advanced series, which will provide insights into complex scenarios and use cases. This will further enhance your expertise and make you better prepared to handle challenging technical questions during the interview.
Remember, the focus of the interview is not solely on getting the correct answers but also on your understanding of the concepts and your ability to apply them effectively. Emphasizing concept learning and practical application will greatly contribute to your success in the Power BI interview. Good luck with your preparation!
Interview Questions- Part 1
Videos of all these questions will be available in the form of a YouTube podcast here. A single video with all questions will be loaded later.
Question 1- What is Power BI?
Power BI is a unified, scalable, and self-service enterprise business intelligence (BI)/Data analytics platform. It helps in data-driven decision-making by converting data into meaningful insights.
Power BI is a business intelligence (BI)/Data analytics platform developed by Microsoft. It is a suite of software products that help businesses collect, analyze, and visualize data. Power BI can be used to create interactive dashboards and reports, as well as to share insights with others.
Power BI is a powerful tool that can be used to improve decision-making in businesses of all sizes. It is easy to use and can be deployed on-premises or in the cloud. Power BI also integrates with other Microsoft products, such as Excel, SharePoint, and Azure.
Official Microsoft Link-
Question 2- What are the components/elements/parts of Power BI?
Power BI Desktop.
Power BI service
Power BI Mobile apps
Power BI Report Builder
Power BI Report Server
Power BI Embedded
Official Microsoft Link-
Question 3- What is Power BI Desktop
Power BI Desktop is a free application that you can install on your local computer to connect to, transform, and visualize your data. It is a powerful tool that can be used to create interactive reports.
Power BI Desktop empowers you to seamlessly connect with diverse data sources and blend them together through data modeling. This process creates a comprehensive data model, laying the foundation for building captivating visuals and assembling them into interactive reports. These reports can then be easily shared with colleagues within your organization.
As a go-to tool for business intelligence projects, Power BI Desktop serves as the primary platform for crafting insightful reports. Once you’ve perfected your reports, the next step involves utilizing the Power BI service to disseminate your findings with others, ensuring seamless collaboration and knowledge sharing across the organization.
- Data connectivity: Power BI Desktop can connect to a wide variety of data sources, including Excel, SQL Server, Azure SQL Database, and Salesforce.
- Data modeling: Power BI Desktop allows you to create data models that can be used to combine data from multiple sources.
- Data visualization: Power BI Desktop includes a variety of visualization tools that can be used to create interactive reports and dashboards.
- Report authoring: Power BI Desktop allows you to create reports that can be shared with other users using the power bi service.
Official Microsoft Link-
Question 4- Why do we need Power BI Desktop?
There are many reasons to need Power BI Desktop. Here are some of the most common reasons.
- To connect to a wide variety of data sources:
- To create data models:
- To create interactive reports
- To publish reports to the Power BI service
As a whole, Power BI Desktop is a powerful tool that can be used for analyzing data, creating reports, and sharing insights. Power BI Desktop offers a great way to visualize your data and make better decisions.
Question 5- What is Power Query?
The Power Query is a data transformation and data preparation tool developed by Microsoft. The Power Query is a powerful tool that provides users with a powerful graphical interface to fetch data from various sources, as well as a Power Query Editor for applying transformations to the data.
It is an integral part of Microsoft Excel, Power BI, and other Microsoft products. Using Power Query, users can connect to various data sources, extract data, transform it, and load it, for analysis and reporting.
Power Query provides a wide range of data transformation capabilities, including filtering, sorting, merging, splitting, pivoting, and aggregating data. This tool allows users to clean and shape their data in order to make it more suitable for analysis without having to write complex codes or manipulate the data manually.
Official Microsoft Link-
Question 6- What is DAX?
Data Analysis Expressions, or simply DAX, is a formula language that is used in Microsoft Power BI, Excel, and other Microsoft products for use in data analysis and calculations. It allows users to create custom formulas and expressions to perform complex calculations, manipulate data, and create calculated columns and measures. It is specifically designed for working with data models and it is widely used to derive insights and make data-driven decisions in the context of business intelligence and data analysis scenarios.
Official Microsoft Link-
Question 7- What is the difference between a Calculated Column and a measure?
Calculated Columns:
A calculated column is a column you add to a table in your data model.
Calculations are performed row by row, and the results are stored in a new column.
It is important to note that the calculation formula is applied individually to each row of data, and the result will be fixed for each row.
Calculated columns cannot use visual filters or slicers.
Calculated columns are best suited to calculations that depend on individual row values, such as concatenations, string manipulations, or simple arithmetic operations.
Calculated columns are static and cannot be used for Dynamic aggregation or calculations across multiple rows or tables.
Measure:
A measure is a calculation that aggregates or computes across multiple rows or tables.
The formulas used in measures adapt to different contexts, thus resulting in dynamic outputs. These calculations are utilized in reporting tools to combine and filter model data based on various attributes.
A measure is used to summarize data in visualizations, such as charts and tables.
Calculations are applied based on visual or report context.
Measures are dynamic and adapt to the data displayed in a visual, depending on filters and slicers applied to the report.
These measures are best suited for use when performing calculations like sums, averages, counts, and many other types of aggregations across a wide variety of datasets.
In summary, Calculated Columns are used for row-level calculations that create new static columns in a table, while Measures are used for aggregations and calculations that summarize data and provide dynamic results in visualizations. The choice between the two depends on the type of calculation and the level of granularity required in the analysis.
Official Microsoft Link-
Question 8- What is the difference between Import, Direct Query, and Live mode?
Import mode: In import mode, the data is copied from the data source into the Power BI model. The data is stored locally (or on the Power Bi cloud storage, when you publish your data to the Power BI service) in the Power BI model, and it is refreshed on a scheduled basis. Using import mode is the best option if you need to perform complex data transformations on your data.
Power BI owns both Data and Model in Import mode
DirectQuery mode: In DirectQuery mode, the data is not stored in the Power BI model. Whenever you interact with a report, the data is retrieved directly from the data source, rather than being queried from the report itself. As a result, the data is always up-to-date, but it can also put a strain on your data source if you have a lot of users interacting with the report at the same time. Using DirectQuery mode is one of the best ways to interact in real-time with a large dataset if you need to make frequent changes to it.
Power BI owns only the model, in direct query mode
Live mode: Live mode is similar to DirectQuery mode, but it is only supported for certain data sources, such as Power BI datasets and Analysis Services. In the Live mode, the data is stored in the source model, it is retrieved from the data source whenever the model is opened, so it is always updated. A live connection allows you to rely on existing data, which can be updated without accessing the report. It can also strain the data source if you have a lot of users opening the model.
In the Live connection, both Data and Model belong to the source
Official Microsoft Link-
Question 9- What are the different types of visuals used in Power BI?
Power BI, a powerful business intelligence tool developed by Microsoft, offers a wide range of visualizations to help users derive meaningful insights from their data. These are the various types of visuals available in Power BI that can be leveraged to create compelling and informative reports.
1. Bar Charts: Bar charts are one of the most commonly used visualizations in Power BI. They represent data using rectangular bars of varying heights, where the length of each bar corresponds to the value being measured. Bar charts are effective in comparing data across different categories or time periods.
2. Line Charts: Line charts are ideal for displaying trends over time. They connect data points using straight lines, allowing users to visualize patterns, fluctuations, and correlations in their data. Line charts are particularly useful for tracking performance metrics or analyzing time-series data.
3. Pie Charts: Pie charts are excellent for illustrating proportions and percentages. They represent data as slices of a circular pie, with each slice representing a category or segment. Pie charts are useful when showcasing the distribution of data or highlighting the contribution of individual components to the whole.
4. Area Charts: Similar to line charts, area charts depict trends over time. However, in area charts, the area between the line and the x-axis is filled, enhancing the visualization of data progression. Area charts are effective in showcasing cumulative data or comparing multiple series simultaneously.
5. Scatter Plot: Scatter plots are valuable for visualizing the relationship between two variables. Each data point is represented by a dot on a two-dimensional plane, with the x-axis and y-axis representing the variables being compared. Scatter plots enable users to identify correlations, clusters, or outliers within their data.
6. Treemaps: Treemaps are hierarchical visualizations that allow users to display data in nested rectangles. The size and color of each rectangle represent different dimensions of the data, enabling users to explore patterns in a visually engaging manner.
7. Gauge Charts: Gauge charts are ideal for illustrating progress or performance against a goal or target. They resemble speedometers and provide a clear visual indication of how well a particular metric is performing. Gauge charts help users quickly assess performance and make informed decisions based on the displayed data.
8. Map: A map visualization displays data on a geographic map, using shapes or bubbles to represent different regions and data points. There are a few Map Visuals supported by Power BI, known as Map, Shape Map, Filled Map, and Azure Map Visual
9. Table: A table visualization presents data in tabular format, with rows and columns, useful for displaying detailed data and allowing users to sort and filter the information.
10. Matrix: A matrix is similar to a table, but it allows users to summarize data by both rows and columns, making it suitable for displaying aggregated information.
11. Card: A card visualization shows a single value, such as a KPI or a total, as a large numeric value on the report
12. Funnel: A funnel visualization displays data as a series of decreasing stages, illustrating the drop-off or progression between each stage.
13. KPI: A Key Performance Indicator (KPI) visualization presents essential metrics in a simple, easy-to-understand format, typically using icons or color-coded indicators.
14. Key Influencers: This visual helps you to identify the factors that are driving a particular metric. It does this by using machine learning to identify the relationships between different variables in your data.
15. Decomposition Tree: This visual helps you to understand the impact of different factors on a particular metric. It does this by breaking down the metric into its constituent parts and showing you how each part contributes to the overall value.
16. Q&A: This visual allows you to ask natural language questions about your data and instantly receive relevant charts and answers. This feature lets you explore and interact with your data without needing intricate knowledge of Power BI.
Official Microsoft Link-
Question 10: How to do a multi-column sort on a visual in Power BI?
Manual multi-column sort is primarily supported on table visuals. You can do that using Shift + Click on the column headers.
Question 11- What is conditional formatting in Power BI?
In Power BI, conditional formatting allows you to highlight specific data in a visual based on a set of criteria you specify. It can be used in order to make data easier to understand as well as to identify trends or outliers within data sets.
There are several types of conditional formatting in Power BI:
- Color (Font and Background): This type of conditional formatting changes the color of cells based on their values. For example, you could use color to highlight cells that are above or below a certain value or to highlight cells that fall within a specific range.
- Icon: With this conditional formatting technique, you can display icons beside cells, depending on their values. Icons can be utilized to show if a cell is above or below a certain value or if a cell falls within a particular range.
- Data bars: With this type of conditional formatting, a bar is displayed next to cells according to their values. The length of the bar corresponds to the value of the cell.
- Web URL: This conditional formatting displays a hyperlink on the text of cells. When you click on the link, it will open a web page in your browser.
Question 12- What are the three format styles in Power BI conditional formatting?
Gradient based: This type of conditional formatting uses a gradient to change the color of cells based on their values.
Rule based: This type of conditional formatting uses rules to change the color of cells based on their values. Rules can be used to highlight cells that meet certain criteria, such as being above or below a certain value or falling within a specific range.
Field value based: In this type of conditional formatting, one can use measures that return color in text or hexadecimal format. Conditional formatting rules can be written as measures.
Question 13- Under what circumstances does conditional formatting become unavailable or when will you not see the conditional formatting icon (Fx icon) in Power BI?
- Visual conditional formatting is not supported for row and column fields in Matrix
2. Conditional formatting is not supported directly for Pie, Donut, Line, and Area
3. Conditional formatting is not supported for other visuals that use a legend or more than one value (Measure). Example — Bars, stacks, combo visuals
Question 14- What is Interactions in Power BI
In Power BI, the term interactions refers to the ability of visual elements used in a report to interact with each other in a way that shows how they are related. When you interact with one visualization, it can dynamically influence the data displayed in other visuals on the same report page.
Cross-Filtering: With cross-filtering, you can filter data in one visualization based on selections made in another visualization. For instance, if you choose a particular category in a bar chart, other visuals like a line chart or a table will update to display information related to that specific category only.
Cross-Highlighting: Cross-highlighting functions similarly to cross-filtering, but instead of filtering the data, it emphasizes(Highlights/fills) the relevant data points in other visuals.
No Interactions- When interactions are disabled, selecting or interacting with one visualization will not affect the data displayed in other visualizations. If you want to focus on specific data in one visualization without the influence of other visuals, this can be useful.
Or you can be asked if you have a few visuals and you do not want them to filter each other. Or divide the page into 2/3 parts where each part of the page does not filter the other parts, or a slicer should filter or impact only a few visuals. Or we have two bar visuals having the same axis, if I select one value on bar one, only that value should be shown in bar two.
Question 15- What is Field Parameters in Power BI? Or How to create a dynamic Axis using a slicer?
The field parameters feature is released in the May 2022 update of power bi. It allows users to dynamically change the dimensions/Axis/Row or Measures/Values in a visual. This feature allows developers to create a dynamic report where the end-user can change Axis/Row/Column or Measure/values in a visual/page.
For the case of dimension/Axis slicer, field parameter can completely replace the need for bookmarks and unpivot table columns to have a dynamic axis.
For the measure slicer, it replaces the old technique of creating an independent table and then writing down a measure, where one can change the measure in the visual based on slicer selection from the newly created table. In the old technique, there was always an issue when the new measure is having measures with different formats like percent vs number.
The calculation groups is another alternative for Measure Slicer.
Or you can be asked, I have a column bar visual with Category on the x-axis, how can change it to Brand/ Item/ City or state using slicer?
Or You can be asked how to create a dynamic measure slicer.
Question 16-: Before the introduction of field parameters, what were the methods used to achieve dynamic dimensions, axes, or rows in Power BI?
Prior to the availability of field parameters, dynamic dimensions, axes, or rows in Power BI could be achieved through the following approaches:
Bookmarks and Buttons: This method involved creating the necessary visuals for each desired axis or dimension. The selection pane could then be utilized to hide visuals that are not currently needed, leaving only the relevant one visible.
1. Create the visuals required for each axis. For example, if you want to be able to dynamically change the dimension on the x-axis of a Bar chart, you would create one visual for each dimension/Axis that you want to be able to use.
2. Use the selection pane to hide visuals other than one. This will ensure that only one visual is visible at a time.
3. Keep creating bookmarks for each visible visual. This will allow you to quickly switch between visuals by clicking on the corresponding button.
4. Add buttons to the page and use them to show each bookmark. This will make it easy for users to change the dimension on the x-axis by clicking on the button corresponding to the desired dimension.
Unpivoting Columns: Another technique was to manipulate the data model by unpivoting relevant columns(In Power Query), such as item, brand, category, and sub-category, which would result in creating a “Type”(Attribute) column representing the attribute and a “Value” column containing the corresponding values. A slicer could then be employed to filter based on the “Type” column, effectively altering the axis or dimension displayed on the report. The “Value” column would be used on the axis, allowing for dynamic adjustments based on user selections.
- Unpivot(Power Query) the columns like item, brand, category, and sub-category, etc. This will create a new table with two columns: “Type”(Attribute) and “Value”. The “Type” column will contain the name of the dimension, and the “Value” column will contain the value of the dimension.
2. Use the “Type” column in a slicer. This will allow users to filter the data by dimension.
3. Use the “Value” column on the axis. This will make the dimension dynamic.
Question 17-: What is bookmarks in power bi?
Bookmarks in Power BI save the state of a report page. With Power BI bookmarks, you can keep track of the current state of a report’s visuals, filters, and other settings by capturing the state of the bookmarks. You can then save that state for later use or interaction. Bookmarks serve as snapshots of your report’s layout and configuration at a specific point in time. This feature is particularly useful for creating interactive and dynamic reports.
There are two types of bookmarks:
- Personal
2. Report
When you create a bookmark, it records the following information:
Visual States: The visibility of visuals on the report canvas.
Filters: Any filters applied to visuals, pages, or the entire report.
Slicers: The selections made in slicers and other filtering controls.
Sort Order: The sorting order of visuals or fields.
Cross-highlighted visuals: Cross-highlighted applied.
Bookmarks are a powerful tool to improve report usability. They can also be used to create interactive report that allow users to explore the data in different ways. You can then utilize these bookmarks to create interactive experiences for reporting consumers.
For example:
Button Navigation: You can associate bookmarks with buttons or other interactive elements, allowing users to switch between different views or states of the report.
Storytelling: Bookmarks can guide users through a narrative by showing specific views of the data in a sequence.
Comparison: Bookmarks enable users to compare different visualizations or filter combinations with a single click.
Question 18: How can we maintain/pass slicer values across bookmarks?
For that, you can uncheck/deselect the Data Option in the bookmark option. Do it for all applicable bookmarks
Question 19: What is the difference between sumx and sum in power bi?
SUMX is an iterator function, which means it iterates through each row of a table and applies an expression to each row. After the result of the expression has been calculated, it is summed up to get the final result. SUMX has this advantage to be used for calculations performed on a row-by-row basis, such as calculating the sum of Gross Sales based on [Qty] *[Price] for each Order on a row-by-row basis. As you know multiplication should be done at row level. Performing such calculations is made easier with this. With help from values and summarize row level of Sumx can be changed to perform more complex calculation
Gross Sales = Sumx(Sales, Sales[Qty]* Sales[Price])SUM, on the other hand, is not an iterator function. It simply sums up all the values in a column, regardless of any filters applied. This makes SUM less flexible than SUMX, but faster. If you do not need row-by-row calculations, SUM is the right choice.
Sum we can have Sum(Sales[Qty]) and I cannot have Sum( Sales[Qty]* Sales[Price]).
For that, I need to create a new column
Gross = Sales[Qty]* Sales[Price]
Then as new measure
Gross Sales = Sum(Sales[Gross])Question 20: What is the difference between calculate and calculatetable in DAX/Power BI?
The main difference between Calculate and CalculateTable in Power BI is that Calculate applies the expression to the current filter context. In contrast, CalculateTable creates a new table with the expression applied to all rows.
- Calculate takes as input an expression that evaluates to a scalar and returns a scalar value. This means that the expression can only return a single value, such as a number or a text string. An expression is evaluated in a modified filter context. Table expression filters apply a table object as a filter. A table object could be a reference to a model table, but it is more likely to be a function returning a table object. FILTER can be used to apply complex filter conditions, including those that cannot be defined by Boolean expressions.
- Calculate is a function that allows you to calculate an expression in a specific context. Context is the set of filters applied to the measure or expression it is in.
- CalculateTable takes as input an expression that evaluates to a table and returns a table. This means that the expression can return a set of rows, such as a list of customers or a list of products. A table expression is evaluated in a modified filter context. FILTER can be used to apply complex filter conditions, including those that cannot be defined by Boolean expressions.
Question 21: What is the difference between Summarize and SummarizeColumns?
The main difference between Summarize and SummarizeColumns in Power BI is that Summarize allows us to have a row and a filter context within the expression. In contrast, the SUMMARIZECOLUMNS function only allows a filter context and no row context.
Summarize has the first argument as a table/table expression, which is missing in summarizecolumns.
Example: Summarize function needs a table or table expression
SalesSummary =
SUMMARIZE(
Sales,
Sales[Product],
Sales[Category],
"Total Sales", SUM(Sales[SalesAmount]),
"Average Quantity", AVERAGE(Sales[Quantity])
)Summarizecolumns function does not need a table or table expression function.
SalesSummaryColumns =
SUMMARIZECOLUMNS(
Sales[Product],
Sales[Category],
"Total Sales", SUM(Sales[SalesAmount]),
"Average Quantity", AVERAGE(Sales[Quantity])
)Question 22: What is the difference between all and allselected in power bi?
ALL disregard filters on a specific column or table to retrieve unfiltered data for calculations.
ALLSELECTED preserves user selections or filters in the specified column while removing filters from other columns, allowing selective adjustments.
In short, All ignores both the Column filter context and the filter row context. On the other hand, allselected ignore only the row context.
Q23: What is the difference between filtering data in CALCULATE with and without FILTER Expression
CALCULATE function without a FILTER expression, you directly apply filters to specific columns within the calculation. This modifies the filter context and affects how the expression is evaluated. CALCULATE without a FILTER expression applies filters directly to columns, altering the filter context and impacting calculations.
example
Brand 1 Sales = CALCULATE(
SUM(Sales[Gross Sales]),
'Item'[Brand] = "Brand 1"
)
In this, if you have a visual with this new measure and brand, you will see Brand 1’s value with all brands.
Which is equivalent of
Brand 1 Sales = CALCULATE(
SUM(Sales[Gross Sales]),
FILTER(all('Item'[Brand]) , 'Item'[Brand] = "Brand 1")
)
Using the FILTER expression within the CALCULATE function allows you to create a filtered table based on specific conditions. This offers a more flexible approach to defining the filter context and customizing how the calculation interacts with the data. CALCULATE with a FILTER expression constructs a custom filter context by creating a filtered table based on specified criteria and returns values based on that.
Brand 1 Sales = CALCULATE(
SUM(Sales[Gross Sales]),
FILTER('Item', 'Item'[Brand] = "Brand 1")
)
Question 24: I have a sales table with a measure known as gross. The sales table is joined with the geography table based on the city ID column. The geography table contains columns for city ID, city, and state. I have created the following measure:
Rank = RANKX(ALL(Geography[City]), [Gross]) By default, would the rank be in ascending or descending order?
Rank is by default descending in the case of RANKX function.
Also remember, Skip is also a default option from Skip and Dense. There is a new rank function available now, and their rank is by default ascending.
Question 25: If in the above visual, I add a column- city id, which also same granularity as a city, and from the same table, will I get the same ranks?
No, you will get all 1. If a column does not participate in the table argument of the Rankx, and it is used in visual, Rank will be partitioned inside that new column. As city and city id has the same granularity, you will get all 1.
Question 26: What is the difference between skip and dense in Rank?
In the “SKIP” rank option, if there are tied values, the subsequent rank(s) will be skipped, and the next unique rank will be assigned. In other words, if two or more values have the same rank, the next rank will be assigned as if there were no tied ranks. There may be gaps in the ranking sequence as a result.
In contrast, when using the “DENSE” rank option, if tied values exist, they will receive the same rank, and subsequent ranks will be adjusted accordingly. There are no gaps in the ranking in this case.
Question 27: I have a sales table with a measure known as “gross.” The sales table is joined with the geography table based on the city ID column. The geography table contains columns for city ID, city, and state. Please provide me with a measure that will display the top 10 states by gross sales.
If you have been asked to provide only the top 10, you can state that you will use a visual level filter of TOPN on the brand.
However, if you have been specifically asked for a measure, you can use the TOPN function to create a measure like this:
TOP 10 = CALCULATE([Gross], KEEPFILTERS(TOPN(10,ALLSELECTED(Geography[State]),[Net], DESC)))
Question 28: What are the common scenarios in which the Grand Total does not equal the sum of rows in both countable and summable measures?
A common scenario is when one uses the distinctcount function, the grand total may not match the sum of rows.
If you’ve utilized a measure that incorporates row context in the calculation, the grand total might not align correctly with the sum of rows. In such cases, you can rectify these calculations by including values/summarize alongside sumx.
Question 29: Why Date table is very important in Power BI?
While you can specify many reasons. Make sure you include these two
Best practice in model design — When crafting your Power BI model, it’s smart to adopt a central date table strategy. This means creating a dedicated Date table that becomes your go-to reference for anything date-related — from crunching numbers to filtering and analyzing across various tables.
Time Calculations Made Easy- The date table is a must when handling time calculations in Power BI. These calculations involve dates, like last year’s sales or MTD, QTD, and YTD sales. To ensure these calculations run smoothly, you’ll need a continuous sequence of dates. That’s where a date table marked as a date table is useful.
Question 30: What are some best practices related to Time Intelligence or Date Tables?
Or, which type of join is preferable between a Date Table and a Transaction Table?
Or How can errors be avoided when utilizing time intelligence functions?
To ensure the success of Time Intelligence, consider the following steps:
- Utilize a Date Table (Joined with Fact Date): Ensure you have a dedicated Date Table that is joined with your Fact Date in your data model.
- Mark Date Table as a Date Table: Mark your Date Table as a Date Table in Power BI.
- Leverage Date Table Columns: Use columns from the Date Table in your visuals, measures, filters, and slicers.
- Avoid Timestamps in Transactional Dates: Ensure that the date field in your transactional table does not include a timestamp. Keep it as a simple date.
- Comprehensive Date Coverage: Guarantee that your Date Table covers the complete date range needed across all relevant tables and dates.
- Beware of Bidirectional Joins: Be cautious with bidirectional joins involving a Date Table, as they can lead to errors.
Question 31: What do mean by MTD, QTD, and YTD?
There are three common acronyms used in BI and analytics to refer to different time periods within a year: MTD, QTD, and YTD. They are often used for tracking and comparing data over specific time intervals. Here’s what they mean, along with examples using dates:
MTD — Month-to-Date: MTD is the time period from the beginning of the month to the present/selected date.
As an example, if today is August 15th, the MTD period includes data from August 1st to August 15th.
QTD — Quarter-to-Date: QTD refers to the period from the beginning of the quarter to the present/selected date.
Example: If today’s date is August 15th and the current quarter is Q3 (July to September), the QTD period would include data from July 1st to August 15th.
YTD — Year-to-Date: YTD encompasses the time frame from the beginning of the year up to the present/selected date.
Example: If today’s date is August 15th, the YTD period would include data from January 1st to August 15th of the same year.
Question 32: Which functions can I use to obtain MTD, QTD, and YTD results in Power BI? Alternatively, could you provide the formulas for calculating MTD, QTD, and YTD?
One can utilize the TOTALMTD, TOTALQTD, and TOTALYTD functions in conjunction with a measure and date table. Additionally, the DATESMTD, DATESQTD, and DATESYTD functions are available, although it’s important to note that these functions return A table containing a single column of date values, unlike the previous ones which provide scalar value.
For example, assuming I already have a measure sales= SUM(Sales[Net Sales])
Sales MTD = CALCULATE([Sales], DATESMTD('Date'[Date]))
Sales QTD = CALCULATE([Sales], DATESQTD('Date'[Date]))
Sales YTD = CALCULATE([Sales], DATESYTD('Date'[Date]))
Sales MTD1 = TOTALMTD([Sales], 'Date'[Date])
Sales QTD1 = TOTALQTD([Sales], 'Date'[Date])
Sales YTD1 = TOTALYTD([Sales], 'Date'[Date])Question 33: Let’s consider a scenario where the Date table contains data from January 1, 2020, to December 31, 2022, and the Sales table contains data from January 1, 2020, to November 30, 2022. The Sales table is connected to the Date table based on the date column, with the Date table designated as the primary date table(Marked as Date Table). The Sales table features a measure called Net =(SUM(Sales[Net Sales])).
Now, I create a new measure named Sales MTD using the formula CALCULATE([Net], DATESMTD(‘Date’[Date])). I proceed to display this newly created measure on a card visual within a fresh page, without any filters or slicers. The question at hand is, for which specific month will the Sales MTD measure display its value?
The DATESMTD function considers the last available date within the current context. In the absence of any date in the visual or the filter, it defaults to the last date in the column specified within the function. In this case, the last date is December 31, 2022. Consequently, the Sales MTD measure will exhibit data from December 1, 2022, to December 31, 2022. However, since the sales table does not contain data for this particular range, the measure will display a blank result.
Question 34: Let’s explore a scenario in which the Date table encompasses data spanning from January 1, 2020, to December 31, 2022. To this table, I introduce a new calculated column with the formula Dateadd([Date], -1, Year).
Now, let’s consider a specific row where the value of the Date column is January 1, 2020. What date will be present in this row as a result of the calculation?
In this case, you will have a blank result. The Dateadd function requires a continuous sequence of dates, and since there is no date preceding January 1, 2020, the expected value of January 1, 2019, cannot be achieved.
Question 35: Then how can I get the date year before?
You can achieve this by utilizing the Date function. To do so, you simply subtract a year, month, or day from the appropriate segment of the function.
example : DATE(year([Date])-1, month([Date]), day([Date]))
Question 36: Let’s consider a scenario where the Date table contains data from January 1, 2020, to December 31, 2022, and the Sales table contains data from January 1, 2020, to November 30, 2022. The Sales table is connected to the Date table based on the date column, with the Date table designated as the primary date table(Marked as Date Table). The Sales table features a measure called Net =(SUM(Sales[Net Sales])).
Now, I create two new measures
Sales LMTD= CALCULATE([Net], DATESMTD(dateadd(‘Date’[Date],-1,Month)))
Sales Previousmonth= CALCULATE([Net], Previousmonth(‘Date’[Date]))
Now, we can pose the following questions:
1.Are they the same?
2. If I choose the month of October 2022 in a slicer, using the same date table, will both measures will display the same value in the card visuals ?
3. If I select the date range from September 1, 2022, to October 31, 2022, will both measures will display the same value in the card visuals?
Answers:
- Using CALCULATE([Net], DATESMTD(dateadd(‘Date’[Date], -1, Month))) will display data from the last month up to the selected day of month. On the other hand, CALCULATE([Net], Previousmonth(‘Date’[Date])) will show data for the entire previous month. The distinction becomes apparent when you apply a date filter within the middle of a month or when you compare these measures alongside the dates from the date table.
- Yes.
- No, the measure using Previousmonth will display data from August 2023. This is because it calculates the previous month based on the first date in the context. In contrast, the measure using DATESMTD will show data from September 2023, as it utilizes the last date to perform the calculation.
Question 37: Consider a scenario in which the Date table is joined to the sales table based on the date column. Additionally, the data table contains a “Month Year” column. Now, if I were to select a specific month, such as Oct-2022, how can I display a trend for the last 12 months? I want the axis to encompass the months from Nov-2021 to Oct-2022. How can I achieve this?
You cannot achieve this by simply selecting a month from the connected date table. To accomplish this, you need a slicer from an independent or disconnected Date table. You can create a measure as outlined below and then plot it against the “Month Year” column of the connected Date table.
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Question 38: I have the Month Year in the format MMM-YYYY, but when I use it in a visual, the sorting is not correct. What steps should I take to achieve the correct sort order in Power BI?
You need to create an additional column(say month Year sort) with the format YYYYMM using FORMAT([Date], “YYYYMM”) or Year([Date])*100 + Month([Date]) and mark that as sort column for Month Year column using column tools
Question 39: What type of schema is preferred for Power BI modeling?
Power BI generally prefers Star Schemas. The Star Schema involves a central fact table that contains measures and is connected to dimension tables. Dimension tables hold descriptive attributes and are directly related to the fact table. The fact table contains measures of interest, such as sales, profit, and revenue. The dimension tables contain attributes that describe the measures, such as product, customer, and time. This schema design simplifies queries, improves performance, and enhances the user experience by enabling efficient data analysis and exploration.
In a Star schema, it’s important to adhere to the rule that facts should not be directly joined with each other. In addition, no two dimension tables should be directly joined with each other. This design principle helps maintain the simplicity and effectiveness of the schema. It ensures that relationships are established between the central fact table and individual dimension tables, leading to improved query performance and data analysis capabilities.
Question 40: In what scenarios can I use the RELATED function? Alternatively, if I am unable to use the RELATED function while creating a column from another related table, what might be the reason?
The Related function can be used between two related tables, which implies that an active join must exist between the two tables. This function is employed to retrieve data in a one-to-many relationship from the table on the “one” side to the table on the “many” side.
In my upcoming blogs, I will address additional questions. Please let me know your feedback and additional question you want me to cover in the next video.
These questions will also be added to the Free course- “Get Fabricated with Microsoft Fabric, Power BI, SQL, Power Query, DAX”
Learn Microsoft Fabric
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
Don’t forget to subscribe to our:
