Power BI vs Tableau

Power BI is a business analytics service that provides interactive visualizations

and business intelligence capabilities. It allows users to create reports and
dashboards from various data sources, facilitating data-driven decision-making
within organizations.
Power BI offers several advantages, including user-friendly interfaces, robust
data visualization capabilities, integration with other Microsoft products, and
the ability to handle large datasets efficiently. This makes it an ideal choice for
businesses looking to enhance their data analysis processes. Both tools are
used for data visualization, but Power BI is more integrated with Microsoft
products, making it easier for users in Microsoft environments. Tableau offers
more advanced visualization options and is often preferred for complex
analytics tasks.
DAX top 20 functions
• 1. CALCULATE()– The master key for context switching in your data models.
• 2. FILTER()– Build custom logic to isolate exactly the data you need.
• 3. SUM()– A staple. Still, your best friend for adding numbers.
• 4. SUMX()– Performs row-level calculations before summing. Great for complex logic.
• 5. AVERAGE()– Straightforward average, but crucial in reporting.
• 6. AVERAGEX()– Like SUMX, but for averages, with added flexibility.
• 7. IF()– The basic building block for conditional logic.
• 8. SWITCH()– A cleaner, more scalable alternative to nested IFs.
• 9. ALL()– Removes filters to return total or global context.
• 10. REMOVEFILTERS()– Similar to ALL(), but more targeted in scope.
• 11. RELATED()– Fetches a single value from a related table.
• 12. RELATEDTABLE() – Returns an entire table related to the current row.
• 13. VALUES()– Gets distinct values, often used in slicer logic or filtering.
• 14. DISTINCT()– Pulls unique values for lists or clean reporting.
• 15. RANKX()– Adds powerful ranking capability for leaderboards and comparisons.
• 16. EARLIER()– Allows referencing earlier row context — tricky but powerful.
• 17. SELECTEDVALUE() – Ideal for slicers; returns a value if one item is selected.
• 18. DIVIDE()– Built-in error handling for safe division (no #DIV/0!).
• 19. DATESYTD(), DATESMTD(), DATESQTD() – Essential for time intelligence and period comparisons.
• 20. FORMAT()– Give your numbers a polished look with custom formatting.
•
Power Query vs Power Pivot, PBI
Desktop
• Power Query is used for data extraction and transformation, allowing users
to clean and shape their data before analysis. Power Pivot, on the other
hand, is focused on data modeling and allows users to create
relationships between different datasets.
• Power BI Desktop is a free application that allows users to create reports
and dashboards by connecting to various data sources. It provides tools
for data modeling, visualization, and report design.
• Power Pivot is an Excel add-in that allows users to perform powerful data
analysis and create sophisticated data models. It enables users to work
with large datasets efficiently using in-memory technology.
• Power Query is a data connection technology that enables users to
discover, connect, combine, and refine data across a wide variety of
sources. It simplifies the process of preparing data for analysis.
Microsoft’s self-service BI solution; DAX

• Microsoft’s self-service BI solution includes components like Power BI

Desktop for report creation, Power Query for data preparation,
Power Pivot for modeling, and Power BI Service for sharing reports
online.
• Self-service BI empowers end-users to access and analyze data
independently without relying on IT departments. It allows users to
create reports and dashboards tailored to their specific needs.
• DAX (Data Analysis Expressions) is a formula language used in Power
BI for creating custom calculations in reports. It includes functions
for filtering, aggregating, and manipulating data.
Filters, Custom Visuals, GetData

• Filters in Power BI allow users to limit the data displayed in reports or

visuals based on specific criteria. They can be applied at various
levels: visual-level, page-level, or report-level.
• Custom visuals are user-created visualizations that can be imported
into Power BI reports. They extend the standard set of visuals
available in Power BI by providing unique ways to display data.
• GetData is a feature in Power BI that allows users to connect to
various data sources such as databases, Excel files, web services,
and more. It serves as the starting point for importing data into the
application.
PBI Advantages, limitations, PBI vs Power Pivot for Excel

• Advantages include ease of use, integration with other Microsoft tools

(like Excel), real-time dashboard updates, extensive visualization
options, and strong community support.
• Limitations include restrictions on dataset size (for free versions),
dependency on internet connectivity for cloud features, potential
performance issues with very large datasets, and limited
customization options compared to some competitors.
• While both tools allow for creating relationships between tables and
building models, Power BI Desktop supports more advanced
features like direct query connections and real-time collaboration
through the cloud.
Connectivity modes available in Power BI, types of refresh options provided in
Power BI, types of connections in Power BI

• Import Mode: Data is imported into the model.

• DirectQuery Mode: Queries are sent directly to the source database.
• Live Connection: A direct connection to an Analysis Services model or
Azure Analysis Services.
• Refresh options include:
Manual Refresh: Users refresh datasets manually.
• Scheduled Refresh: Automatic refresh at specified intervals.
• DirectQuery: Real-time querying of live databases without needing
refreshes.
• Users can connect to a wide range of sources including SQL Server
databases, Excel files, cloud services (like Azure), web APIs, SharePoint
lists, and many more.
•
Dashboard, Relationships,
standalone table
• A dashboard is a single-page interface that displays key metrics through
visualizations from multiple reports. It provides a high-level overview of
performance indicators at a glance.
• Relationships can be defined manually by specifying primary and foreign
keys or automatically through the model's auto-detect feature which
identifies relationships based on matching column names.
• No, only one active relationship can exist between two tables at any time;
however, multiple inactive relationships can be defined which can be
activated using DAX functions like USERELATIONSHIP.
• Yes, it’s possible to have standalone tables without relationships; they can
still be used within measures or calculations but won't interact with other
tables' filters or context.
•
Calculate, Get Data, Publish Reports
• CALCULATE modifies the filter context applied to a calculation by
evaluating an expression under new filter conditions specified by
additional arguments.
• Users connect to data by selecting "Get Data" from the Home tab in
Power BI Desktop. They then choose their desired source type (e.g.,
Excel file or SQL database) and follow prompts to establish the
connection.
• Reports created in Power BI Desktop can be published directly to the
Power BI Service by clicking on the "Publish" button found on the
Home ribbon after signing into your account.
•
Calc column, measure, fact table,
dimension
• A calculated column adds new column values based on DAX formulas
applied row by row within a table during data load or refresh processes
• A measure performs calculations on aggregated values dynamically based
on filters applied within reports; they are recalculated based on user
interactions with visuals.
• A Fact Table contains quantitative data (metrics) for analysis while
Dimension Tables contain descriptive attributes related to facts (like time
periods or product categories).
• Connections are made by establishing relationships based on common
fields (keys) such as foreign keys from Fact Tables pointing back to
primary keys in Dimension Tables.
•
Languages PBI, bookmarks, drill-
through, data stored in PBI
• DAX for calculations within models and M language used within
queries for transforming raw data during import processes via
Power Query.
• The easiest navigation method involves using bookmarks for quick
access between views or utilizing drill-through features within
reports for detailed insights into specific metrics.
• Data in Power BI can be stored in several ways depending on the
version being used. In Power BI Desktop, data is stored in an internal
model using a highly optimized columnar storage format. For users
of Power BI Service, datasets are stored in Microsoft Azure, allowing
for cloud-based accessibility and collaboration.
Row-level security, general
formatting, different views in PBI
• Row-level security (RLS) in Power BI restricts data access at the row level
based on user roles. This means that different users can see different
data within the same report or dashboard, ensuring that sensitive
information is only visible to authorized individuals.
• General formatting of Power BI data enhances report readability and
consistency. It facilitates stakeholders to view visualizations fast and
understand the insights without any confusion caused due to different
formats, thereby enhancing the overall experience of the users.
• Report View: For designing and building reports.
• Data View: To inspect and analyze the underlying dataset.
• Model View: For managing relationships between tables and visualizing
the data model structure.
•
Versions of PBI, building blocks of
PBI
• Power BI Desktop: A free version for creating reports and dashboards.
• Power BI Pro: A paid version that allows sharing, collaboration, and more
extensive features.
• Power BI Premium: Offers enhanced performance, larger storage
capacities, and advanced features for enterprise-level deployments.
• Datasets: The data sources imported into Power BI.
• Reports: Collections of visuals based on datasets.
• Dashboards: Single-page summaries created from multiple reports.
• Workspaces: Collaborative environments for managing content and
sharing insights among users.
•
Power BI toolkit, content pack
• Power Query: For data extraction and transformation.
• Power Pivot: For data modeling and creating relationships.
• DAX (Data Analysis Expressions): A formula language for calculations.
• Power BI Service: The online platform for sharing and collaboration.
• A content pack in Power BI is a pre-packaged collection of related
dashboards, reports, datasets, and Excel files that can be shared
with users. Content packs can be created by organizations or
sourced from third-party providers to facilitate easy access to
common analytics resources.
Define bi-directional cross filtering, three fundamental concepts of DAX

• Bi-directional cross filtering allows filters to flow both ways between

related tables in a data model. This means that when a filter is
applied to one table, it affects related tables as well, enabling more
dynamic interactions within visualizations.
• Filter Context: The set of filters applied to a calculation based on user
selections or report filters.
• Row Context: The context of a single row when performing
calculations across tables.
• Evaluation Context: The combination of filter context and row context
that determines how DAX formulas are evaluated.
•
Custom visual file, familiar sources for data in the Get Data menu in Power BI,
categories of data type

• Custom visual files can be used in Power BI to enhance visualizations beyond standard options. Users can import these visuals
from external sources or create their own using tools like TypeScript or D3.js to meet specific analytical needs or provide unique
insights.
• Common sources available in the Get Data menu include:
Excel files
• SQL Server databases
• SharePoint lists
• Online services (like Google Analytics)
• Web APIs
• What are the categories of data types?

• Data types in Power BI include:

Text: For string values.
• Number: For integer or decimal values.
• Boolean: For true/false values.
• Date/Time: For date and time values.
•
•
•
Name some commonly used tasks in the Query
Editor, Grouping in Power BI, Responsive slicers

• Common tasks performed in Query Editor include:

Data transformation (e.g., merging, appending queries)
• Cleaning data (e.g., removing duplicates, filtering rows)
• Changing data types for columns
• Creating calculated columns based on existing data
• Grouping in Power BI refers to consolidating rows based on shared
attributes to summarize data. This allows users to aggregate values (like
sums or averages) for categories within a dataset, making it easier to
analyze trends and patterns.
• Responsive slicers adjust their size and layout dynamically based on the
available space within a report. This feature enhances user interaction by
ensuring that slicers remain accessible and visually appealing across
different screen sizes and devices.
M Language, SUM(), Calc column,
measure
• M language is a functional programming language used in Power Query for data
manipulation and transformation tasks. It allows users to write complex queries
for importing, shaping, and cleaning data before loading it into Power BI.
• An example of a DAX function is SUM(), which calculates the total sum of a
column's values. For instance, Total Sales = SUM(Sales[SalesAmount]) computes
the total sales amount from the Sales table.
• A calculated column is computed during data load and stored within the model as
part of the table; it evaluates row-by-row. A measure calculates results
dynamically based on filters applied during report interactions, providing
aggregated results across rows.
• Generally, measures provide better performance compared to calculated columns
because they are computed at query time rather than during data load.
Measures also reduce memory usage since they do not increase model size like
calculated columns do.
•
Relationships in PBI
• The different types of relationships include:
One-to-One (1:1): Each record in one table corresponds to one record in
another table.
• One-to-Many (1:N): One record from one table relates to multiple records
in another table.
• Many-to-Many (N:N): Records from both tables can relate to multiple
records from each other.
• Relationships can be created by navigating to the Model view in Power BI
Desktop and dragging lines between fields that represent keys in related
tables. Alternatively, users can define relationships through the "Manage
Relationships" dialog box by specifying primary and foreign keys.
Types of joins
• Yes, it is possible to have standalone tables without relationships with
other tables; these tables can still be used within measures or
calculations but won't interact with other tables' filters or context.
• Inner Join: Returns only matching rows from both tables.
• Left Join: Returns all rows from the left table and matching rows from
the right table; non-matching rows will have nulls.
• Right Join: Returns all rows from the right table with matching rows
from the left; non-matching rows will have nulls.
• Full Outer Join: Returns all rows when there is a match in either left
or right table records.
•
What are the major differences between Power BI and Excel, Power BI
Desktop vs Power BI Service.

• Data Visualization: Power BI offers advanced visualization options compared to

Excel's basic charts.
• Data Capacity: Power BI handles larger datasets more efficiently than Excel's
limitations.
• Collaboration: Power BI facilitates real-time collaboration through its cloud
service while Excel primarily focuses on individual use unless integrated with
SharePoint or similar tools.
• Power BI Desktop is a desktop application used for creating reports and
dashboards offline, while Power BI Service is an online platform that enables
sharing, collaboration, and real-time updates of reports created with Desktop.
The Service also supports cloud storage options.
• A workspace in Power BI is a collaborative environment where users can create,
manage, and share reports, dashboards, datasets, and other resources with
team members or stakeholders within an organization.
How do you optimize Power BI reports for
performance, slicer vs filter

• To optimize reports for performance:

Limit dataset size by removing unnecessary columns or rows.
• Use measures instead of calculated columns where possible.
• Minimize visuals on each report page to reduce rendering time.
• Implement query folding where applicable when using DirectQuery mode.
• A slicer is a visual element that allows users to select values directly on
reports for filtering data interactively; it provides an intuitive way to
segment information visually. Filters apply more broadly at visual-level,
page-level, or report-level without direct interaction through visuals.
SUM() vs SUMX(), Time intelligence, Semantic model

• SUM() calculates total values directly from a specified column across all
rows without any additional context. In contrast, SUMX() evaluates an
expression over a table row-by-row before summing up results; it's useful
when calculations involve multiple columns or require context-specific
evaluations.
• Time intelligence functions allow users to perform calculations based on
dates effectively—enabling comparisons over time periods such as year-
to-date totals or previous year comparisons easily within DAX formulas.
• A Semantic Model defines how data elements relate within databases or
datasets while providing business logic through measures, hierarchies,
metadata definitions etc., facilitating easier understanding and analysis
by end-users without needing deep technical knowledge about
underlying structures.
What are the major differences between visual-level, page-level, and report-level filters in
Power BI
List the most common techniques for data shaping.

• Visual-Level Filters: These filters apply to individual visualizations, allowing users to

control what data is displayed in specific charts or graphs. They do not affect other
visuals on the report page.
• Page-Level Filters: These filters affect all visuals on a single report page, providing a
cohesive view of data that meets specific criteria across multiple visuals.
• Report-Level Filters: These filters apply to all pages within a report, ensuring that the
same filter criteria are consistently applied throughout the entire report.
• Common data shaping techniques include:
Filtering Rows: Removing unnecessary data based on specific criteria.
• Changing Data Types: Ensuring each column has the correct data type for analysis.
• Merging Queries: Combining data from multiple sources into a single dataset.
• Grouping Data: Aggregating data based on shared attributes to summarize information.
•
How is the Schedule Refresh feature designed to work? What information is needed to create a map in Power
Map?

• The Schedule Refresh feature allows users to automate the updating of

datasets in Power BI Service at specified intervals. Users can set up
refresh schedules based on their needs (e.g., daily, weekly) to ensure that
reports and dashboards reflect the most current data without manual
intervention.
• To create a map in Power Map, you need:
Geographic data (like addresses, cities, or coordinates) to plot locations
accurately.
• Data fields that represent values or metrics to visualize (e.g., sales figures).
• A clear understanding of how you want to represent this data visually (e.g.,
heat maps or clustered points).
•
Which in-memory analytics engine does Power Pivot use? Mention important components of SSAS?

• Power Pivot uses the VertiPaq engine, which is an in-memory analytics

engine designed for fast data compression and retrieval. This engine
enables efficient querying and processing of large datasets within Excel
and Power BI.
• Important components of SQL Server Analysis Services (SSAS) include:
Data Models: Structures that define how data is organized and related.
• Cubes: Multi-dimensional structures that allow for efficient querying and
analysis of data.
• Dimensions and Measures: Dimensions provide context (e.g., time,
geography), while measures are quantitative values used for analysis
(e.g., sales amounts).
• Data Mining: Tools for discovering patterns and insights from large
datasets.
•
What are the three fundamental concepts of DAX?
Name the variety of Power BI Formats?

• Filter Context: The set of filters applied to a calculation based on user

interactions or report filters.
• Row Context: The context of a single row when performing calculations
across tables.
• Evaluation Context: The combination of filter context and row context that
determines how DAX formulas are evaluated.
• Power BI supports various formats including:
PBIX files: Used for Power BI Desktop reports.
• PBIT files: Template files for reports without data.
• Power BI Service dashboards and reports, which are stored online for
sharing and collaboration.
•
What are the different stages in the working of Power BI?

• The different stages include:

Data Preparation: Importing and transforming raw data using Power
Query.
• Data Modeling: Creating relationships between datasets using Power
Pivot.
• Report Creation: Designing reports with visuals in Power BI Desktop.
• Publishing and Sharing: Uploading reports to Power BI Service for
collaboration and distribution.
•
Power BI Gateways / Some
applications of PBI/ Story in Power BI
• Power BI offers two types of gateways:
Personal Gateway: For individual users to refresh on-premises datasets without needing IT
support; ideal for personal use cases.
• Enterprise Gateway: For organizations needing centralized management of scheduled refreshes
across multiple users; it supports larger-scale deployments with enhanced security.
• Q. Mention some applications of Power BI?

• Applications of Power BI include:

Sales performance analysis dashboards.
• Financial reporting and forecasting tools.
• Marketing campaign performance tracking.
• Operational reporting for supply chain management.
• You can depict a story in Power BI by using a combination of visuals that guide viewers through
insights step-by-step. This includes using narrative text boxes, sequential visuals, and interactive
elements like drill-throughs to engage users with the data effectively.
•
•
KPIs, slicer, reshape data
• Key Performance Indicators (KPIs) in Power BI are metrics used to evaluate
success against defined objectives. They provide visual representations of
performance against targets, often displayed as colored indicators (green,
yellow, red) based on thresholds set by users.
• A slicer is a visual element in Power BI that allows users to filter data
interactively within reports. It provides an intuitive way to segment
information by selecting specific categories or values directly from the
report interface.
• Data can be reshaped in Power BI using features within Power Query such as
filtering rows, changing column types, merging queries, pivoting/unpivoting
columns, or grouping data based on specific attributes.
Star vs snowflake, USERELATIONSHIP, M:M
relationships

• A star schema involves a central fact table directly connected to dimension

tables, making queries easier but likely redundant. On the other hand, a
snowflake schema normalizes dimension tables into multiple related
tables that tend to have reduced redundancy but make queries more
complex.
• The USERELATIONSHIP function in DAX allows users to specify which
relationship between two tables should be used when calculating
measures. This is particularly useful when there are multiple
relationships between two tables but only one can be active at any time.
• Many-to-many relationships can be handled by creating bridge tables that
connect two tables indirectly. This involves creating an intermediary table
that contains unique values from both related tables, allowing for
effective filtering and aggregation.
VALUES / DISTINCT, CROSSFILTER / dynamic data updates

• Both VALUES and DISTINCT return unique values from a column; however,
VALUES also returns blank values if they exist within the column while
DISTINCT does not. VALUES is often used when needing context-aware
results based on filter context.
• The CROSSFILTER function changes how filters propagate between two
related tables during calculations. It allows users to specify whether
filtering should be unidirectional or bidirectional when evaluating
measures involving relationships.
• Dynamic data updates can be managed through scheduled refreshes set up
in the Power BI Service or by using DirectQuery mode which allows real-
time querying against live databases without needing manual refreshes.
•
Row Context vs Filter Context, Append vs Merge

• Row context refers to the current row being evaluated during

calculations within DAX formulas; it’s automatically applied when
iterating over rows. Filter context refers to filters applied from slicers
or other visuals that affect what rows are considered during
calculations.
• 'Append Query' combines rows from two or more tables into one
table vertically (stacking), while 'Merge Query' combines columns
from two tables horizontally based on matching keys (joining).
Append is used when datasets have similar structures; merge is used
when datasets contain related information.
SAMEPERIODLASTYEAR(), What if
parameter
• You can compare last year’s numbers to this year’s numbers by
creating measures using DAX functions like SAMEPERIODLASTYEAR()
or PREVIOUSYEAR() along with time intelligence functions that allow
easy year-over-year analysis within your reports.
• The ‘What If’ parameter allows users to create scenarios by changing
input values dynamically within reports. This feature enables
stakeholders to visualize potential outcomes based on varying
assumptions or conditions without altering underlying datasets
directly.
Power BI app, data flows in PBI
• A Power BI App is a packaged collection of related dashboards and
reports published together for easier distribution among users
within an organization. Apps provide a streamlined way for teams to
access relevant insights without navigating through individual
reports.
• Dataflows allow users to create reusable ETL processes within the
cloud-based environment of Power BI Service. Benefits include
centralized management of transformation logic, improved
collaboration across teams, and streamlined access to cleaned
datasets across multiple reports.
Fabric, OneLake Data Hub

• Fabric refers to Microsoft's unified analytics platform designed for

seamless integration across various analytics services including
Azure Data Factory, Azure Synapse Analytics, and others. It aims to
simplify workflows by providing an end-to-end solution for
managing analytics processes.
• OneLake Data Hub is the centralized repository, in fact, designed for
vast amounts of structured and unstructured data, spread across a
variety of services, within a single, cohesive ecosystem that offers
easier access and integration between multiple analytics tools and
provides robust governance features.