[DAX]ALLSELECTED vs. ALL

ALLSELECTED()

Syntax

ALLSELECTED(<tableName> or <columnName>)

  • 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.

Running Total by Date with ALLSELECTED().
Running Total by Date (Start from 1/1/2021)
Running Total by Date (Start from 1/1/2025)
  • 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.

Use ALLSELECTED with RANKX
The results are correct.

ALL()

Syntax

ALL( <table> or <column>)

  • This function is useful for clearing filters and creating calculations on all the rows in a table.

Summary

Here are some tips that I use to decide on which DAX to use.

  1. If you want to ignore all the slicers effects of your result, then “ALL” would come in handy.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store