Daily DAX : Day 295 ALLSELECTED

Understanding the ALLSELECTED DAX Function in Power BI

The ALLSELECTED DAX function in Power BI removes filters from a table or column while keeping filters applied by slicers or visuals in a report. It’s ideal for calculations based on user-selected data.

Syntax

ALLSELECTED([table | column[, column[, ...]]])

• table: Table to remove filters from.
• column: Specific column(s) to remove filters from.

How It Works

ALLSELECTED removes filters from the query context but retains user-driven filters (e.g., slicers). Unlike ALL, which ignores all filters, ALLSELECTED respects user selections, making it dynamic.

Common Use Cases

1. Percentage of Total: Calculate the percentage of sales for selected data.
   

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

2. Ranking: Rank items within user-selected filters.
   

   Rank = 
   RANKX(
       ALLSELECTED(Sales[Product]), 
       CALCULATE(SUM(Sales[Amount]))
   )
       

3. Dynamic Totals: Show totals for selected regions or periods.
   

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

Example

For a report with Year and Region slicers, calculate each product’s sales as a percentage of the total for the selected year and region:

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

Key Notes

• ALLSELECTED vs. ALL: ALLSELECTED respects slicer filters; ALL ignores them.
• Use for dynamic calculations like percentages or rankings based on user selections.
• Be cautious with large datasets due to potential performance impact.