Sometimes we would struggle about whether to use ALL or ALLSELECTED. In this article, I will introduce some tips and scenarios to help decide when to use.
ALLSELECTED(<tableName> or <columnName>)
- ALLSELECTED removes/ignores all filters but takes into account the overarching context in the report page.
- For example, most of the time we will have the “Date Slicer” on our reporting page, and we want to show the reports based on “Date Slicer”. Therefore, we would choose to use ALLSELECTED. In other words, ALLSELECTED(Dates) only takes into account the “Dates” in the report page.
Scenario 1. Running Total
Assuming we have “Date Slicer” on the reporting page, and we would like the running total to be based on the Date selected.
- What if we use “ALL” instead of “ALLSELECTED” in the measure above?
ALL removes/ignores all filters, and therefore ALL(Dates) ignores the “Date Slicer” in the reporting page. The result will be wrong accordingly.
- However, if we don’t have “Date Slicer” on the reporting page, ALL(Dates) works well and gets the correct running total.
Scenario 2. Ranking with RANKX()
We would like rank the products based on the QTY Sold. Here comes the use of ALLSELECTED.
Usually, I always choose the first column (“Product” in this example) and apply to ALLSELECTED. That is, ALLSELECTED([Product]).
ALL( <table> or <column>)
- ALL() ignores all the filters, regardless of where they are coming from. (either slicers or filter panes)
- This function is useful for clearing filters and creating calculations on all the rows in a table.
Here are some tips that I use to decide on which DAX to use.
- If you have “slicers” on the reporting page, then more of the time “ALLSELECTED” would be the one to use.
- If you want to ignore all the slicers effects of your result, then “ALL” would come in handy.