The All Selected function is a powerful tool in Power BI’s Data Analysis Expressions (DAX) language. It allows you to remove filters from a specific column or table in your data model, while still maintaining the other filters applied to the rest of your report.
What is the All Selected function?
The All Selected function is used to remove filters from a specific column or table in your data model. It returns a table that includes all the values from the specified column or table, regardless of any filters that may be applied to that column or table in the report.
By default, Power BI applies filters to all visuals in a report based on the interactions between them. However, there may be instances where you want to remove filters from a specific column or table to see the unfiltered data. This is where the All Selected function comes in handy.
How to use the All Selected function
To use the All Selected function, you need to specify the column or table you want to remove filters from. The syntax for the function is as follows:
ALLSELECTED([column_name])For example, if you have a table called “Sales” and you want to remove filters from the “Region” column, you would use the following formula:
ALLSELECTED(Sales[Region])This will return a table that includes all the values from the “Region” column, regardless of any filters that may be applied to it in the report.
Examples of using the All Selected function
Here are a few examples to help you understand how the All Selected function works:
Example 1:
You have a report that includes a table visual showing sales data by region. You also have a slicer visual that allows users to filter the data by product category. By default, when a user selects a specific product category in the slicer, the table visual will show the sales data only for that category. However, if you want to see the sales data for all product categories, regardless of the slicer selection, you can use the All Selected function.
ALLSELECTED(Sales[Region])This will return a table that includes all the values from the “Region” column, regardless of the slicer selection.
Example 2:
You have a report that includes a line chart visual showing sales data over time. You also have a slicer visual that allows users to filter the data by region. By default, when a user selects a specific region in the slicer, the line chart visual will show the sales data only for that region. However, if you want to see the sales data for all regions, regardless of the slicer selection, you can use the All Selected function.
ALLSELECTED(Sales[Date])This will return a table that includes all the values from the “Date” column, regardless of the slicer selection.
Conclusion
The All Selected function in Power BI’s DAX language is a powerful tool that allows you to remove filters from a specific column or table in your data model. It helps you see the unfiltered data while still maintaining the other filters applied to the rest of your report. By understanding how to use this function effectively, you can enhance your data analysis and reporting capabilities in Power BI.