DAX

Dax Function: ALLSELECTED

Q: What does the ALLSELECTED function do?

The ALLSELECTED function returns a table or column respecting slicer filters but ignoring visual filters.

Q: When should I use ALLSELECTED?

You should use ALLSELECTED for calculations that need to adapt to slicer filters while ignoring visual-level filters.

Q: How is ALLSELECTED different from ALL?

ALL ignores all filters, while ALLSELECTED respects slicer and report-level filters.

Q: Can ALLSELECTED be combined with other functions?

Yes, ALLSELECTED works well with DIVIDE, CALCULATE, and RANKX for dynamic calculations.

Category: Filter Functions

The ALLSELECTED function in Power BI is a DAX function used to return all the values in a table or column as filtered by the current context of a report, excluding any direct filters applied within the visual.

Purpose

The ALLSELECTED function is designed to:

Provide insight into data that respects slicer and report-level filters but ignores visual-level filters.
Enable advanced calculations like percentages, rankings, and contributions that depend on the context of selections made in slicers or filters.

Type of Calculations

This function performs:

Context-Sensitive Aggregations: Returns values based on the slicer context while disregarding visual filters.
Dynamic Calculations: Adapts to the filters applied at a higher level in the report, making it ideal for context-aware measures.

Practical Use Cases

Percentage of Grand Total: Calculate the percentage of the selected data compared to the overall total based on slicer selections.
Dynamic Rankings: Create rankings that adapt to the slicers but ignore visual-specific filters.
Contextual Insights: Provide insights tailored to the user’s current slicer-based selections.


ALLSELECTED(<table>)</table>

Parameter	Type	Description
`table`	Table	The table or column to be returned while respecting slicer filters and ignoring visual filters.

How Does ALLSELECTED Dax Works

The ALLSELECTED function:

Respects higher-level filters applied via slicers or filter panes.
Ignores filters directly applied to visuals in the current report.
Returns the unfiltered table or column for calculations.

Example Formula:

Selected Sales % =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales)),
0
)

This formula calculates the percentage of sales based on the user’s current slicer selections.

What Does It Return?

The ALLSELECTED function returns:

Table: A table with all rows respecting slicer or report-level filters but ignoring visual-level filters.

When Should We Use It?

Interactive Dashboards: For measures that depend on slicer-based user interactions.
Advanced Analytics: In scenarios requiring context-sensitive aggregations, such as rankings or percentages.
Comparative Analysis: To compare a subset of data (from slicer filters) to the total or grand total.

Examples

Basic Usage :


ALLSELECTED(Sales)

Returns the Sales table, respecting slicer filters but ignoring visual filters.

Column Usage


Selected Total Sales =
CALCULATE(
SUM(Sales[Amount]),
ALLSELECTED(Sales[Region])
)

Calculates the total sales by region, considering slicer filters but excluding visual filters.

Advanced Usage


Contribution % =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales)),
0
)

Calculates the contribution of a selected region or product to the total sales.

Tips and Tricks

Layered Filters: Use ALLSELECTED when working with slicers, and you need to ignore visual-specific filters.
Combine with DIVIDE: Pair with DIVIDE for percentage calculations to handle division by zero.
Debugging: Use tools like DAX Studio to verify how ALLSELECTED is applied in your measures.

Performance Impact of ALLSELECTED DAX Function:

Dataset Size: Efficient for small to medium datasets but may slow down with large datasets when combined with complex calculations.
Context: Ensure the context of filters is clear to avoid unintended results.

Related Functions You Might Need

ALL: Removes all filters, including slicer filters, from a table or column.
REMOVEFILTERS: Removes filters but can be applied selectively to visual or report filters.
VALUES: Returns the unique values in a column considering all filters.

Want to Learn More?
For more information, check out the official Microsoft documentation for ALLSELECTED You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the ALLSELECTED function do?

It returns a table or column respecting slicer filters but ignoring visual filters.

2. When should I use ALLSELECTED?

Use it for calculations that need to adapt to slicer filters while ignoring visual-level filters.

3. How is ALLSELECTED different from ALL?

ALL ignores all filters, while ALLSELECTED respects slicer and report-level filters.

4. Can ALLSELECTED be combined with other functions?

Yes, it works well with DIVIDE, CALCULATE, and RANKX for dynamic calculations.

5. Does ALLSELECTED improve performance?

It depends on the dataset and calculation complexity. Use it judiciously for optimal performance.

Dax Function: ALLNOBLANKROW

Dax Function: CALCULATE