Sample Dataset

EmployeeID	FullName	Country	Department	JoinDate	MonthlyIncome	Bonus	Active	ManagerID	JobLevel	Grade
E001	Alice Johnson	USA	HR	2020-03-15	4500	500	TRUE	M001	1	B
E002	Bob Smith	USA	IT	2019-06-10	6000	800	TRUE	M002	2	A
E003	Clara Lee	India	Finance	2021-01-20	5000	400	TRUE	M003	1	B
E004	David Brown	UK	IT	2018-09-05	6200	1000	FALSE	M002	3	A
E005	Emma Davis	India	HR	2022-11-01	4700	300	TRUE	M001	1	C
E006	Frank Wilson	UK	Finance	2017-05-30	5300	600	TRUE	M003	2	B

Dataset and DAX and Power BI Dashboard here : https://github.com/slidescope/payroll-analysis-project

1. Which departments contain at least one employee with a Grade “A”?

🔹 Use: CONTAINS

DepartmentsWithGradeA =
FILTER(
    VALUES(EmployeePayroll[Department]),
    CONTAINS(
        EmployeePayroll,
        EmployeePayroll[Department], EmployeePayroll[Department],
        EmployeePayroll[Grade], "A"
    )
)

This DAX expression returns a table of unique departments (VALUES(EmployeePayroll[Department])) where there exists at least one employee with Grade “A” in that department.

VALUES(EmployeePayroll[Department])

This returns a table of distinct department values from the EmployeePayroll table.

FILTER(...)

You are filtering this list of departments using a condition that checks whether there exists any employee with Grade “A” in that department.

3. CONTAINS(EmployeePayroll, EmployeePayroll[Department], EmployeePayroll[Department], EmployeePayroll[Grade], "A")

This checks if the EmployeePayroll table contains at least one row where:

• EmployeePayroll[Department] = the current department (from VALUES(...) iteration), and
• EmployeePayroll[Grade] = “A”

💡 EmployeePayroll[Department] appears twice because:

• First instance: Column in the table to check
• Second instance: Value to match (from the outer iterator)

---

2. Show only JobLevel in visuals if it’s being used as a filter

🔹 Use: ISINSCOPE ( Measure )

JobLevelInfo :=
IF(
    ISINSCOPE(EmployeePayroll[JobLevel]),
    "Filtered Level: " & SELECTEDVALUE(EmployeePayroll[JobLevel]),
    "All Levels"
)

It dynamically returns a text string based on whether the column EmployeePayroll[JobLevel] is being filtered (or drilled down into) in the current context (like a matrix, table, or visual).

---

🔍 Step-by-step Breakdown:

1. ISINSCOPE(EmployeePayroll[JobLevel])

• Returns TRUE if JobLevel is in the current scope—for example, in a row-level context like a matrix or visual grouped by JobLevel.
• Returns FALSE if JobLevel is not specifically in scope (e.g., you’re looking at a total or higher-level summary).

---

2. SELECTEDVALUE(EmployeePayroll[JobLevel])

• Returns the single value of JobLevel in the current filter context.
• If multiple values are present, it returns blank.
• Used here safely because ISINSCOPE ensures we’re only calling it when the context is for a single JobLevel.

---

3. IF(...)

• If the visual is currently scoped by JobLevel, it returns:
• "Filtered Level: <JobLevel>"
• If not, it returns:
• “All Levels”
  

📊 Example Use Case

Suppose you have a matrix visual like this:

JobLevel	Salary	JobLevelInfo
1	4000	Filtered Level: 1
2	6000	Filtered Level: 2
Total	10000	All Levels

---

3. Rank employees by MonthlyIncome within their Department

🔹 Use: RANKX, ALL

RankInDept :=
RANKX(
    ALL(EmployeePayroll[FullName]),
    CALCULATE(SUM(EmployeePayroll[MonthlyIncome])),
    ,
    DESC
)

Great — you’re using RANKX, a powerful DAX function for ranking values within a context.

---

📘 Here’s your DAX measure:

RankInDept =
RANKX(
    ALL(EmployeePayroll[FullName]),
    CALCULATE(SUM(EmployeePayroll[MonthlyIncome])),
    ,
    DESC
)

Let’s break it down:

---

🔍 What does RANKX do?

RANKX(<table>, <expression>, <value>, <order>)
Ranks each row in the given table based on the result of evaluating the expression.

---

✅ What this measure does:

• Ranks all employees (based on FullName)
• According to their total MonthlyIncome
• In descending order — highest income gets Rank 1

---

🔧 Key Components:

Part	Explanation
ALL(EmployeePayroll[FullName])	Removes any filters on FullName so we rank among all employees
CALCULATE(SUM(EmployeePayroll[MonthlyIncome]))	Income expression used for ranking
DESC	Sorts from highest to lowest income

---

💡 What you’ll see in a table visual:

FullName	MonthlyIncome	RankInDept
Emma Davis	9500	1
Alice Johnson	8700	2
…	…	…

⚠️ Misleading Measure Name Alert:
Even though your measure is named RankInDept, it actually ranks employees across all departments, because you are ranking over ALL(EmployeePayroll[FullName]) — not filtering by department.

---

✅ To Rank Within Each Department:

Use this version instead:

RankInDept :=
RANKX(
    ALL(EmployeePayroll[FullName]),
    CALCULATE(SUM(EmployeePayroll[MonthlyIncome])),
    ,
    DESC
)

🔁 Replace with:

RankInDept :=
RANKX(
    FILTER(
        ALL(EmployeePayroll),
        EmployeePayroll[Department] = MAX(EmployeePayroll[Department])
    ),
    CALCULATE(SUM(EmployeePayroll[MonthlyIncome])),
    ,
    DESC
)

✅ Now it ranks only within the same department, based on total income.

---

4. Display distinct Grades present in the current filter context

🔹 Use: VALUES

ListOfGrades =
CONCATENATEX(
    VALUES(EmployeePayroll[Grade]),
    EmployeePayroll[Grade],
    ", "
)

---

5. Get Total MonthlyIncome for only Grade “B” even when filters are applied

🔹 Use: REMOVEFILTERS, FILTER

IncomeOnlyGradeB :=
CALCULATE(
    SUM(EmployeePayroll[MonthlyIncome]),
    REMOVEFILTERS(EmployeePayroll[Grade]),
    FILTER(EmployeePayroll, EmployeePayroll[Grade] = "B")
)

6. Compare average salary by grade

🧠 Learning Goal: Use AVERAGEX, grouping with SUMMARIZE ( measure )

❓Question:
Write a measure to show average income per Grade.

✅ Answer:

AvgIncomePerGrade =
AVERAGEX(
    VALUES(EmployeePayroll[Grade]),
    CALCULATE(SUM(EmployeePayroll[MonthlyIncome]))
)

---