Data Analytics

📊 Data Analyst Interview Series — Part 21

Guys, let's continue our Data Analyst Interview Series.

In this part, let's focus on Advanced DAX and practical Power BI scenarios. These questions will help you understand how to solve real business problems using DAX.

1️⃣ What is the difference between ALL, ALLSELECTED, and ALLEXCEPT?

Sample Answer:

These three DAX functions are used to modify filter context.

• ALL removes filters from the specified table or column.

• ALLSELECTED removes certain visual-level filters while retaining external selections, such as slicer selections.

• ALLEXCEPT removes filters from a table except for the specified columns.

Example:

Sales by Region % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(Region)
    ),
    0
)

This calculates each region's sales as a percentage of the total across all regions, while removing filters from the Region table.

2️⃣ What is the difference between RELATED and LOOKUPVALUE?

Sample Answer:

Both functions can retrieve values from another table, but they work differently.

RELATED retrieves a value from the one-side of an existing relationship when there is an appropriate row context.

Example:

Customer Country =
RELATED(Customers[Country])

LOOKUPVALUE searches for a value based on specified matching conditions.

Example:

Customer Country =
LOOKUPVALUE(
    Customers[Country],
    Customers[CustomerID],
    Sales[CustomerID]
)

I prefer RELATED when a suitable relationship already exists. LOOKUPVALUE is useful when I need to search using specific matching conditions.

3️⃣ How do you calculate the rank of products based on sales?

Sample Answer:

I can use the RANKX function.

Product Rank =
RANKX(
    ALL(Products[ProductName]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

This ranks products from highest to lowest sales.

DENSE ranking means tied products receive the same rank, and the next rank is not skipped.

4️⃣ What is the difference between RANKX and TOPN?

Sample Answer:

RANKX assigns a ranking to records based on an expression.

TOPN returns a table containing the top N records based on a specified expression.

For example:

Top 5 Products =
TOPN(
    5,
    ALL(Products[ProductName]),
    [Total Sales],
    DESC
)

I would use RANKX when I need to display rankings and TOPN when I need to identify a subset of top-performing records.

5️⃣ How do you calculate the average sales per customer?

Sample Answer:

First, I calculate total sales:

Total Sales =
SUM(Sales[SalesAmount])

Then:

Average Sales per Customer =
DIVIDE(
    [Total Sales],
    DISTINCTCOUNT(Sales[CustomerID]),
    0
)

This calculates average sales per distinct customer appearing in the Sales table.

If the business requires average sales across all registered customers, including those with no purchases, I would use the Customers dimension table for the denominator instead.

6️⃣ How would you calculate the percentage contribution of each product category to total sales?

Sample Answer:

First, I create a Total Sales measure:

Total Sales =
SUM(Sales[SalesAmount])

Then:

Category Contribution % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(Products[Category])
    ),
    0
)

This calculates each category's contribution while removing the category filter.

Other report filters, such as date or region, remain applicable.

7️⃣ What is the difference between ISBLANK and COALESCE?

Sample Answer:

ISBLANK checks whether an expression returns a blank value.

Example:

📊 Data Analyst Interview Series — Part 16

In this part, let's focus on Power BI Data Modeling and Relationships — an extremely important area for Data Analyst interviews.

1️⃣ What is data modeling in Power BI?

Sample Answer:

Data modeling is the process of organizing tables and defining relationships between them so that Power BI can correctly analyze and visualize the data.

For example, I might have:

Sales → Fact table
Customers → Customer dimension
Products → Product dimension
Date → Date dimension

I would create relationships between these tables so that users can analyze sales by customer, product, date, region, and other dimensions.

2️⃣ What is a fact table?

Sample Answer:

A fact table generally contains business transactions and numerical values that we want to analyze.

For example, a Sales fact table could contain:

• Order ID
• Customer ID
• Product ID
• Date
• Quantity
• Sales Amount
• Cost

Fact tables are usually larger than dimension tables and contain the measurable events of the business.

3️⃣ What is a dimension table?

Sample Answer:

A dimension table contains descriptive information used to analyze the data in a fact table.

For example, a Customer dimension could contain:

• Customer ID
• Customer Name
• City
• Country
• Customer Segment

Dimensions provide the context through which we analyze facts.

For example:

Sales by Country
Sales by Product
Sales by Customer

4️⃣ What is a one-to-many relationship?

Sample Answer:

A one-to-many relationship means one record in one table can be associated with multiple records in another table.

For example:

One customer can have many sales transactions.

Customers[CustomerID] → Sales[CustomerID]

Customer table = One side
Sales table = Many side

This is one of the most common relationships in a Power BI star schema.

5️⃣ What is cardinality in Power BI?

Sample Answer:

Cardinality describes how records in two tables relate to each other.

The common types are:

• One-to-one
• One-to-many
• Many-to-one
• Many-to-many

For example, if one customer can have many transactions, the relationship is one-to-many.

Choosing the correct cardinality is important because an incorrect relationship can produce inaccurate results.

6️⃣ What is cross-filter direction?

Sample Answer:

Cross-filter direction determines how filters travel between related tables.

The common options are:

• Single
• Both

In a typical star schema, I prefer single-direction filtering from dimension tables toward fact tables.

For example:

Date → Sales
Product → Sales
Customer → Sales

Using bidirectional filtering unnecessarily can introduce ambiguity and make the model harder to understand or troubleshoot.

7️⃣ What is a many-to-many relationship?

Sample Answer:

A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another table.

For example, one customer could belong to multiple groups, and one group could contain multiple customers.

Many-to-many relationships can be useful in certain scenarios, but I would not use them unnecessarily.

If possible, I would redesign the model using a bridge table to make the relationships clearer and easier to manage.

8️⃣ Why is a star schema preferred in Power BI?

Sample Answer:

A star schema separates business transactions from descriptive information.

For example:

FactSales
→ DimCustomer
→ DimProduct
→ DimDate
→ DimRegion

This structure is easier to understand, generally performs well, and makes DAX calculations and filtering more predictable.

It also makes the model easier to maintain as the reporting requirements grow.

9️⃣ What is a bridge table and when would you use one?

Sample Answer:

A bridge table is an intermediate table used to resolve certain complex relationships, especially many-to-many scenarios.

For example, suppose customers can belong to multiple segments and each segment can contain multiple customers.

The bridge table helps control the relationship and makes the data model more structured.

📊 Data Analyst Interview Series — Part 13

📊 Power BI Interview Questions

Part 13 — Power BI Fundamentals

1️⃣ What is Power BI and why is it used?

Sample Answer:

Power BI is a business intelligence and data visualization tool from Microsoft. It helps analysts connect to different data sources, clean and transform data, create data models, build interactive reports and dashboards, and share insights with stakeholders.

For example, I can connect Power BI to SQL Server, Excel, or CSV files, transform the data using Power Query, create a data model, write DAX measures, and build an interactive sales dashboard.

2️⃣ What are the main components of Power BI?

Sample Answer:

The major components I commonly work with are:

• Power BI Desktop — Build reports and data models.
• Power BI Service — Publish, share, and manage reports online.
• Power BI Mobile — View reports on mobile devices.
• Power Query — Connect, clean, and transform data.
• DAX — Create calculations and measures.
• On-premises Data Gateway — Connect Power BI Service to on-premises data sources.

Together, these components support the complete reporting and analytics process.

3️⃣ What is the difference between Power BI Desktop and Power BI Service?

Sample Answer:

Power BI Desktop is mainly used for developing reports and data models, while Power BI Service is a cloud-based platform used to publish, share, collaborate, and manage those reports.

For example:

Power BI Desktop → Build the report → Publish → Power BI Service → Share with users.

4️⃣ What is Power Query in Power BI?

Sample Answer:

Power Query is used for extracting, cleaning, and transforming data before loading it into the Power BI model.

For example, I can use Power Query to:

• Remove duplicates
• Handle missing values
• Change data types
• Split columns
• Merge datasets
• Append datasets
• Filter records
• Create calculated transformation columns

Power Query is essentially used for the ETL part of the process.

5️⃣ What is the difference between Power Query and DAX?

Sample Answer:

Power Query is primarily used to prepare and transform data before it enters the model, whereas DAX is used to create calculations within the Power BI model.

For example:

Power Query → Remove duplicates, merge tables, clean customer names.

DAX → Calculate Total Sales, Profit Margin, Year-over-Year Growth.

A simple way to remember it is:

Power Query = Data preparation

DAX = Data analysis and calculations

6️⃣ What is a data model in Power BI?

Sample Answer:

A data model defines how different tables are structured and connected within Power BI.

For example, I might have:

Sales
Customers
Products
Date

The Sales table can be connected to Customers, Products, and Date using relationships.

A well-designed data model makes reporting more accurate, easier to maintain, and more efficient.

7️⃣ What is a relationship in Power BI?

Sample Answer:

A relationship connects two tables using a common column.

For example:

Customers
↓
Sales[CustomerID]

This allows Power BI to understand which sales transactions belong to which customers.

Common relationship types include:

• One-to-many
• Many-to-one
• One-to-one
• Many-to-many

In most analytical models, one-to-many relationships are commonly used.

8️⃣ What is a star schema and why is it important?

Sample Answer:

A star schema is a data modeling approach where a central fact table is connected to multiple dimension tables.

For example:

FactSales
→ DimCustomer
→ DimProduct
→ DimDate
→ DimRegion

The fact table contains measurable business transactions, while dimension tables provide descriptive information.

I prefer a star schema because it makes the model easier to understand, improves filtering behavior, and generally supports better reporting performance.

🎓 𝟰 𝗖𝗲𝗿𝘁𝗶𝗳𝗮𝘁𝗶𝗼𝗻𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻 𝟮𝟬𝟮𝟲 🚀

Want to build job-ready skills and strengthen your resume? Start learning these in-demand technologies

📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/45vk5ph

💫 𝗙𝘂𝗹𝗹𝘀𝘁𝗮𝗰𝗸 :- https://pdlink.in/3SuUeuD

☁️ 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 :- :- https://pdlink.in/48kyZjt

🛡️ 𝗔𝗜 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝗶𝗻𝗴 :- :- https://pdlink.in/4fWJVID

🔁 𝗦𝗵𝗮𝗿𝗲 this with your friends and classmates!

NumPy indexing is useful for selecting specific rows, columns, and values.

8️⃣ What is slicing in NumPy?

Sample Answer:

Slicing allows us to extract a portion of an array.

Example:

arr = np.array([10, 20, 30, 40, 50])

print(arr[1:4])

Output:

[20 30 40]

For a two-dimensional array:

matrix = np.array([
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9]
])


print(matrix[:, 1])

Output:

[2 5 8]

Here, the colon selects all rows, while 1 selects the second column.

9️⃣ What is vectorization in NumPy?

Sample Answer:

Vectorization means performing operations on entire arrays without explicitly writing Python loops.

Example:

arr = np.array([10, 20, 30, 40])

result = arr * 2

print(result)

Output:

[20 40 60 80]

Vectorization makes numerical code simpler and is generally faster than equivalent Python-level loops for suitable operations.

🔟 What is broadcasting in NumPy?

Sample Answer:

Broadcasting allows NumPy to perform operations on arrays with different shapes when their dimensions are compatible.

Example:

arr = np.array([10, 20, 30])

result = arr + 5

print(result)

Output:

[15 25 35]

Here, NumPy automatically applies the scalar value 5 to every element.

Broadcasting is useful because it allows calculations across arrays without manually repeating values.

📌 Double Tap ❤️ For More

Data Analyst Interview Series — Part 26

We've covered Python fundamentals and intermediate concepts. Now let's move to one of the most important libraries for Data Analysts:

📊 NumPy — Numerical Python

1️⃣ What is NumPy and why is it used in Data Analytics?

Sample Answer:

NumPy stands for Numerical Python. It is a Python library used for numerical computing and working with arrays.

It provides powerful mathematical operations and efficient data manipulation capabilities.

NumPy is commonly used for:

• Working with multidimensional arrays
• Mathematical calculations
• Statistical operations
• Matrix operations
• Data preprocessing
• Scientific computing

Example:

import numpy as np

numbers = np.array([10, 20, 30, 40])

print(np.mean(numbers))

Output:

25.0

2️⃣ What is an array in NumPy?

Sample Answer:

A NumPy array is a data structure that stores elements, typically of the same data type, in one or more dimensions.

Example:

import numpy as np

arr = np.array([10, 20, 30, 40])

print(arr)

Output:

[10 20 30 40]

Unlike a standard Python list, NumPy arrays support efficient vectorized numerical operations.

3️⃣ What is the difference between a Python list and a NumPy array?

Sample Answer:

Python lists and NumPy arrays both store collections of values, but they have different characteristics.

Python List:
• Can contain different data types.
• Supports general-purpose collection operations.
• Does not directly support element-wise arithmetic.

NumPy Array:
• Usually stores elements of the same data type.
• Supports vectorized mathematical operations.
• Is generally more memory-efficient for homogeneous numerical data.
• Supports multidimensional arrays.

Example:

List:

numbers = [1, 2, 3]
print(numbers * 2)

Output:

[1, 2, 3, 1, 2, 3]

NumPy:

arr = np.array([1, 2, 3])
print(arr * 2)

Output:

[2 4 6]

4️⃣ What is the difference between a one-dimensional and two-dimensional array?

Sample Answer:

A one-dimensional array contains elements in a single sequence.

Example:

arr1 = np.array([10, 20, 30])

A two-dimensional array contains elements arranged in rows and columns.

Example:

arr2 = np.array([
    [10, 20],
    [30, 40]
])

A two-dimensional array is useful for representing tabular numerical data.

5️⃣ What are the important attributes of a NumPy array?

Sample Answer:

NumPy provides several attributes to understand an array's structure.

• ndim — Number of dimensions.
• shape — Size of each dimension.
• size — Total number of elements.
• dtype — Data type of elements.
• itemsize — Size of each element in bytes.

Example:

arr = np.array([[10, 20, 30], [40, 50, 60]])

print(arr.ndim)
print(arr.shape)
print(arr.size)
print(arr.dtype)

Output:

2
(2, 3)
6
int64

The exact dtype can depend on the system and how the array is created.

6️⃣ What is the difference between reshape() and resize()?

Sample Answer:

Both functions are used to change the dimensions of an array.

reshape() returns an array with a new shape without changing the original array's shape.

Example:

arr = np.array([1, 2, 3, 4, 5, 6])

new_arr = arr.reshape(2, 3)

resize() changes the shape of an array in place when using the ndarray method.

Example:

arr = np.array([1, 2, 3, 4])

arr.resize(2, 2)

print(arr)

Output:

[[1 2]
 [3 4]]

The requested shape must contain a compatible number of elements for reshape().

7️⃣ How do you access elements in a NumPy array?

Sample Answer:

We can use indexing to access individual elements.

Example:

arr = np.array([10, 20, 30, 40])

print(arr[0])
print(arr[2])

Output:

10
30

For a two-dimensional array:

matrix = np.array([
    [10, 20],
    [30, 40]
])

print(matrix[1, 0])

Output:

30