 |
VOOZH | about |
|
VOOZH | about |
Power BI is a data visualization and business intelligence tool by Microsoft that helps in connecting, transforming and analyzing data. In interviews, questions often cover its core features like data modeling, DAX formulas, dashboards and reports as well as scenario-based problem-solving.
Power BI is a data visualization tool developed by Microsoft. It enables users to establish connections with diverse data sources, transform and manipulate data, generate interactive reports and dashboards and share insights with others. Power BI is extensively used in organizations to analyze data and make informed decisions based on data-driven insights.
BI stands for Business Intelligence which refers to collecting, analyzing and delivering business data to support decision-making in organizations This system uses a variety of tools, applications and practices to transform raw data and organise them into valuable insights. By doing so, companies can make informed decisions, spot trends and improve their overall performance.
Power BI is a useful tool with many features. Some notable features include:
Below are some key differences between Power BI and Tableau:
| Feature | Power BI | Tableau |
|---|---|---|
| Calculation Language | Uses DAX (Data Analysis Expressions) for creating measures. | Uses MDX (Multidimensional Expressions) along with other functions for measures and dimensions. |
| Visualization Library | Offers a wide range of built-in visualizations with easy customization. | Provides an extensive visualization library with deeper customization options. |
| Data Handling Capacity | Handles moderate data volumes effectively but struggles with very large datasets. | Can handle massive datasets and complex queries efficiently. |
| Ease of Use | Simple interface, suitable for beginners as well as professionals. | A bit tough, best suited for experienced users and professionals. |
| Cloud Support | Cloud integration is available but with certain limitations on large-scale handling. | Strong cloud support, well-suited for large enterprises. |
| Feature | Power BI | Excel |
|---|---|---|
| Tabular Reports | Not ideal for creating traditional tabular reports. | Well-suited for detailed tabular reports. |
| Duplicate Tables | Cannot display duplicate tables directly. | Allows users to display duplicate tables easily. |
| Reports | Provides interactive and personalized reports with cross-filtering between charts. | Limited interactivity and advanced cross-filtering between charts is not available. |
| Analytics | Offers easy-to-use analytics, mainly focused on data visualization. | Provides advanced analytics with complex formulas, pivot tables and statistical functions. |
| Applications | Best for creating dashboards, KPIs and alerts with real-time insights. | Useful for calculations, financial models and newer charts exist but lack direct data model connections. |
| Aspect | Calculated Columns | Calculated Tables | Measures |
|---|---|---|---|
| Definition | Adds a new column to an existing table using a DAX formula. | Creates a new table using DAX expressions instead of pulling from a data source. | Performs on-the-fly calculations using DAX functions. |
| Where Created | Can be created in both Report View and Data View. | Can be created in both Report View and Data View. | Can only be created in Report View. |
| Use Case | Useful when data from the source isn’t in the required format (e.g., splitting full names into first/last names). | Helpful for storing intermediate or user-requested data inside the model. | Best for business calculations like sales forecasting, running totals, growth %, YOY comparisons, etc. |
| Storage | Stored in the model and increases data size. | Stored in the model and increases data size. | Not stored and calculated dynamically, so they don’t increase model size. |
| Performance | Can slow down model performance if many columns are added. | May impact performance depending on table size. | More efficient since they calculate results on demand. |
| Aspect | Power BI Dataset | Power BI Report | Power BI Dashboard |
|---|---|---|---|
| Purpose | Data storage and modeling | Data visualization and analysis | Data presentation and navigation |
| Function | Stores, cleans and prepares data for reporting. | Displays visualizations and insights from a dataset. | Organizes visuals and reports into a single-page view. |
| Created In | Built in Power Query or Power BI Desktop. | Created and edited in Power BI Desktop. | Created in Power BI Service by pinning visuals from reports. |
| Interactivity | No direct user interactivity. | Fully interactive with filters, slicers and drill-through. | Limited interactivity (supports drill-through but not full filtering). |
| Export Options | No export or print options. | Allows export of visuals and reports. | Can export dashboard visuals but with limited options. |
Data can be refreshed in Power BI in the following manner:
There are five different components of Power BI.
Power Query in Power BI is a data connection and transformation tool that helps you import, clean and reshape data before using it for analysis and reporting. It allows users to connect to multiple data sources, apply transformations and prepare the data without needing complex coding.
Power BI supports a wide variety of data sources that you can connect to for building reports and dashboards. These can be broadly grouped as:
1. File Sources
2. Database Sources
3. Online Services
4. Cloud & Big Data Sources
5. Other Sources
Data modelling in Power BI is the process of structuring and organizing data so that it can be used effectively for analysis and reporting. It involves:
A key part of data modelling is creating relationships between two tables, which connect data using common fields like CustomerID or ProductID. These relationships can be:
Power Pivot in Power BI is a data modelling component that lets you build relationships between tables, create calculations and manage large datasets efficiently. It uses DAX (Data Analysis Expressions) to define calculated columns, tables and measures making it possible to perform advanced analysis beyond simple visualizations.
| Aspect | Power View | Power Map |
|---|---|---|
| Purpose | Used for creating interactive reports and dashboards. | Used for 3D geographical data visualization. |
| Visualization Style | Tables, charts, cards, maps and interactive visuals. | 3D maps, globe view and time-based animations. |
| Focus | Focuses on data analysis and storytelling through visuals. | Focuses on geospatial analysis (data across locations and over time). |
| Interactivity | Highly interactive with filtering, slicing and drill-down. | Allows exploration of data over time and geography. |
| Integration | Part of Excel add-ins and connected with Power BI for reports. | Available as an Excel add-in (later called 3D Maps). |
Power Q&A in Power BI is a feature that lets users ask questions about their data in natural language and get instant answers in the form of charts or visuals. Instead of writing queries, you can type (or even speak) questions like “Total sales by region” or “Top 5 products by revenue” and Power BI automatically generates the appropriate visualization.
Data can be filtered in Power BI using various filters. There are:
1. Visual-Level Filters
2. Page-Level Filters
3. Report-Level Filters
4. Drillthrough Filters
5. Slicers
6. Top N Filters
7. Cross-Filtering / Cross-Highlighting
8. Advanced Filters
greater than, less than, contains or custom formulas.| Aspect | Slicers | Filters |
|---|---|---|
| Purpose | Allows interactive selection of values to filter visuals. | Used to apply static or pre-set filters on visuals, pages or reports. |
| User Interaction | Users can click and select values directly on the report. | Usually configured in the filter pane and not directly clickable on the report. |
| Visual Representation | Appears as a visual element (list, dropdown, slider, date picker) on the report. | Not a visual and exists in the filter pane. |
| Scope | Can filter one or multiple visuals depending on connections. | Can be applied at visual, page or report level. |
| Ease of Use | More intuitive and user-friendly for end-users. | Less interactive and primarily used to control report behavior. |
Power BI provides a variety of visualization types to represent data effectively. These include:
DAX or Data Analysis Expressions is a formula language used in Power BI, Power Pivot and Analysis Services to perform data calculations and analysis. It allows you to create calculated columns, measures and tables to enhance your data model.
DAX Syntax:
MeasureName = VAR x = <expression>
RETURN x
DAX Example:
TotalSalesWithTax = VAR SalesAmount = SUM(Sales[Amount])
RETURN SalesAmount * 1.1
Benefits of Using Variables in DAX
Three fundamental concepts of DAX are as follows:
1. Row Context
2. Filter Context
3. Context Transition
Some of the most commonly used DAX functions are listed below:
| Function | Purpose | Example |
|---|---|---|
| COUNTROWS | Counts the total number of rows in a table or table expression (including duplicates) | COUNTROWS of Sales counts all rows in the Sales table |
| DISTINCTCOUNT | Counts the number of unique values in a column | DISTINCTCOUNT of Sales[CustomerID] counts unique customers in Sales |
The CALCULATE function in DAX is used to modify the filter context of a calculation and evaluate an expression under those filters. It is one of the most important DAX functions because it allows dynamic calculations based on specific conditions.
Syntax:
MeasureName = CALCULATE(<expression>, <filter1>, <filter2>, …)
Example:
TotalSalesIndia = CALCULATE(SUM(Sales[Amount]), Sales[Country] = "India")
Filter Context is the set of filters applied to a calculation or measure that determines which data is included in the result. It is created automatically by slicers, filters, rows, columns or measures in a report.
For example, If you have a measure Total Sales = SUM(Sales[Amount]) and a page filter Region = "India", the filter context ensures that Total Sales shows only the sales from India.
Power BI allows users to add customized visual elements to their reports and dashboards. These custom visuals, which can be created by users or third-party developers, offer unique ways to present and analyze data beyond the standard visuals provided by Power BI.
By exploring the Custom Visuals Gallery in Power BI, users can discover and integrate these custom visuals into their reports to enhance the presentation and analysis of data. These custom visuals are especially useful when industries require specialized chart types or when the standard visuals don't meet specific visualization requirements. Overall, custom visuals in Power BI enable users to create more engaging and tailored reports and dashboards.
| Aspect | Row Context | Filter Context |
|---|---|---|
| Definition | Applies row by row in a table. | Applies filters to a set of data for a calculation. |
| How It Works | Each row is treated individually for the calculation. | Only the rows that meet the filter conditions are considered. |
| Usage | Used in calculated columns and iterators (e.g., SUMX). | Used in measures, slicers, report filters or CALCULATE function. |
| Example | Profit = Sales[Amount] – Sales[Cost] (for each row) | Total Sales for Region = "India" shows sum only for India. |
Measures are calculations in Power BI, Power Pivot or Analysis Services that compute results dynamically based on the current filter context. Unlike columns, they aren’t stored in the table, instead they are evaluated dynamically whenever you interact with visuals, slicers or filters in your report.
Example:
Total Sales = SUM(Sales[Amount])
It shows the total sales dynamically based on applied filters like region or year.
Common DAX Aggregate Functions:
Power BI has three distinct views, each serving a unique purpose:
1. Report View
2. Data View
3. Model View
A Conditional Column in Power BI is a column created based on a condition or rule applied to existing columns. It allows you to categorize or transform data without writing DAX formulas.
Syntax Example:
if [SalesAmount] > 1000 then "High"
else if [SalesAmount] >= 500 then "Medium"
else "Low"
Here:
Append Queries in Power BI is a feature in Power Query Editor that allows you to combine rows from two or more tables into a single table. It’s like stacking tables on top of each other.
Syntax Example:
Table.Combine({Table1, Table2})
Here:
| Aspect | Visual | Report | Dashboard |
|---|---|---|---|
| Definition | A single chart, graph or tile representing data. | A collection of multiple visuals on one or more pages. | A single-page view that pins visuals from one or more reports. |
| Purpose | Display one data insight. | Analyze data in detail with multiple visuals. | Monitor key metrics and KPIs at a glance. |
| Interactivity | Can be filtered, sliced or highlighted individually. | Fully interactive with slicers, filters and drill-through. | Limited interactivity and mostly view-only. |
| Creation | Created directly in Power BI Desktop. | Created in Power BI Desktop with multiple visuals. | Created in Power BI Service by pinning report visuals. |
| Share | Cannot export individually. | Can be exported as PDF or PowerPoint. | Can share dashboard links or embed in apps. |
1. Removing Null Values:
Example: If a CustomerID column has null values, they will be removed.
2. Removing Duplicate Values:
Example: If the Sales table has duplicate OrderID, selecting OrderID and removing duplicates will keep only unique orders.
A KPI (Key Performance Indicator) in Power BI is a visual used to track progress toward a specific goal or business target. It helps measure performance by comparing actual values against a target value.
Example: If you want to track Sales Performance:
Add-ins in Power BI are extra tools or extensions that you can integrate with Power BI to extend its functionality. They allow users to bring in advanced visuals, connect with other applications or use specialized features that aren’t available by default.
Types of Add-ins:
Example:
We can write dax query like:
Avg Sales per Customer =
DIVIDE(
SUM(Sales[Amount]),
DISTINCTCOUNT(Sales[CustomerID])
)
We can write dax query like:
Unique Products = DISTINCTCOUNT(Sales[ProductID])
We can write dax query like:
Employees_After2020 =
CALCULATE(
COUNTROWS(Employee),
Employee[JoiningYear] > 2020
)
We can write dax query like:
YoY Sales Growth =
DIVIDE(
[Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date])),
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
)
We can write dax query like:
Sales % by Region =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales[Region]))
)
Use a Top N filter in the visual:
Product and Total SalesTop5 Products =
TOPN(5, SUMMARIZE(Sales, Sales[Product], "Sales", SUM(Sales[Amount])), [Sales], DESC)
We can use Power Q&A by typing the question directly into the Q&A visual.
Syntax:
total sales in 2023
Create a One-to-Many relationship by CustomerID in Customers → CustomerID in Orders.
Syntax:
Customers[CustomerID] 1 → * Orders[CustomerID]
Use Append Queries in Power Query.
Syntax:
Combined Sales = Append(Online Sales, Store Sales)
Use Conditional Column in Power Query with rule: If Marks > 40 → Pass, Else → Fail.
Syntax:
Result = if [Marks] > 40 then "Pass" else "Fail"
Use a Matrix visualization with Region as rows, Month as columns and Sales as values.
Syntax:
Rows = Sales[Region]
Columns = Sales[Month]
Values = SUM(Sales[Amount])
Answer: Use a Line Chart with Date on X-axis and Sales on Y-axis.
Create Actual Sales and Target Sales measures, then use a KPI visual to compare.
Syntax:
Actual Sales = SUM(Sales[Amount])
Target Sales = SUM(Targets[TargetAmount])
Use a Card visual with a measure:
DynamicTitle = "Sales Report for " & SELECTEDVALUE(Date[Year])
MasterDate = Table.Distinct(Table.Combine({Date1, Date2, Date3}))
Create a Running Total measure in DAX:
Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Date), Date[Date] <= MAX(Date[Date])))