Another commonly used DAX function in PowerBI is the ALL function. It is used to clear filters that may be applied. When visualizing data in PowerBI the data is often filtered either using the filter pane or even as part of the measure itself. Clearing these filters give access to Grand Total like measures without having to add more columns. We can use the ALL function with filter and SUMX to calculate the TotalQty contribution of each state to the annual sales as shown below. The result of the DAX function will look similar to the one below
As we can see the EXALL_1 column contains the sum of all orders for all states. The formula for the measure looks like below
EXAll_1 = CALCULATE( SUM('Fact Order'[Quantity]), ALL('Dimension City'[State Province]) )We can add an additional slicer like year to see how year still has the filter applied.
The SQL equivalent of this function would be similar to a Select statement without a where condition.
Please Consider Subscribing
