POWER BI LEVEL 8 — ADVANCED DAX: FILTER(), VALUES(), SELECTEDVALUE() & DYNAMIC CALCULATIONS
Now let's move into DAX functions that help you build more dynamic Power BI reports.
These functions are especially useful when your calculation needs to react to slicers, selections, or the current report context.
🔹 1. FILTER()
You already know that FILTER() can create a filtered table.
Example:
High Value Sales =
CALCULATE(
[Total Sales],
FILTER(
Sales,
Sales[SalesAmount] > 10000
)
)
This keeps only transactions where SalesAmount is greater than 10,000.
The important thing to understand:
• "FILTER()" works with a table and evaluates a condition for each row.
• Use it when your filtering requirement is more complex than a simple condition.
🔹 2. VALUES()
"VALUES()" returns the unique values from a column based on the current filter context.
Example:
Customer Count =
COUNTROWS(
VALUES(Sales[CustomerID])
)
This counts the unique customers visible in the current context.
For example:
• Without filters → 1,000 customers
• Region = West → 250 customers
• Region = South → 300 customers
The result changes according to the report filters.
🔹 3. VALUES() vs DISTINCT()
Both can return unique values, but they aren't identical in every situation.
A useful beginner-level rule:
• "DISTINCT()" → returns unique values from a column.
• "VALUES()" → returns unique values while also being sensitive to the current DAX context and can include a blank value when appropriate.
In advanced DAX, "VALUES()" is extremely useful for understanding what values are currently available in the filter context.
🔹 4. SELECTEDVALUE()
This is one of the most useful functions for interactive reports.
Suppose you have a Region slicer.
You can write:
Selected Region =
SELECTEDVALUE(
Sales[Region],
"Multiple Regions"
)
If the user selects:
• West → Result: West
• West + South → Result: Multiple Regions
If nothing is selected, the result can also return the alternate value depending on the filter context.
🔹 5. SELECTEDVALUE() with Dynamic Titles
You can use SELECTEDVALUE() to make report titles dynamic.
Example:
Sales Title =
"Sales Performance - "
&
SELECTEDVALUE(
Sales[Region],
"All Regions"
)
If the user selects West:
• Sales Performance - West
If multiple regions are selected:
• Sales Performance - All Regions
This makes dashboards much more interactive.
🔹 6. HASONEVALUE()
"HASONEVALUE()" checks whether exactly one unique value exists in the current filter context.
Example:
Single Region Selected =
IF(
HASONEVALUE(Sales[Region]),
"One Region",
"Multiple Regions"
)
If exactly one region is selected:
• One Region
Otherwise:
• Multiple Regions
🔹 7. SELECTEDVALUE() vs HASONEVALUE()
They are related but serve different purposes.
"HASONEVALUE()" asks:
• "Is exactly one value selected?"
"SELECTEDVALUE()" asks:
• "What is that selected value?"
For example:
SELECTEDVALUE(Sales[Region]) returns the actual region.
HASONEVALUE(Sales[Region]) returns TRUE or FALSE.
🔹 8. Dynamic KPI Calculation
Suppose you want a KPI to change based on a slicer containing:
• Sales
• Profit
• Orders
A measure can use the selected value to determine what should be displayed.
Conceptually:
Selected KPI =
SWITCH(
SELECTEDVALUE(KPI[KPI Name]),
"Sales", [Total Sales],
"Profit", [Total Profit],
"Orders", [Total Orders]
)
Now one visual can display different KPIs based on the user's selection.
This is called a:
• 👉 Dynamic Measure
🔹 9. SWITCH()
"SWITCH()" is extremely useful for dynamic DAX.
Instead of writing many nested IF statements: