Whenever designing multi-level filters in DAX, there are considerations to make to ensure that there is no overlap at every filter level, be it product category, region, or any other dimension, so as to allow proper aggregation of data. Here is how this can be done:
Use CALCULATE with Specific Filter Contexts: CALCULATE is a function in DAX that adds or modifies filter contexts. For instance, if you want to analyze sales per region within product categories, you may compress marketing regions within CALCULATE for each product dimension. This gives the user the option to choose how his calculation should be.
Apply ALL or REMOVEFILTERS to Control Filter Scope: This may happen in cases where other filters come into play or may even conflict with other filters. In this case, ALL or REMOVE FILTERS can be used to suppress some filters for a while and focus on applying only the appropriate context. For example, if one wishes to synthesize all the regions but still remain with a certain category, then the ALL operation would be called for. This helps in managing complicated filtering situations.
Take Advantage of TREATAS for Cross-Table Filtering: In cases where the multi-level filters involve tables that are not directly connected and which have advanced relationships, TREATAS can provide functionality close to that of a filter and help apply one table's filter to another. This is particularly the case in many cases where one cannot simply enforce relationships. Estimating the correctness of TREATAS, one can speak of advanced cross-filtering.
The above methods can be dissected to mean that you will not have extreme restrictions within your DAX formulas. All filters will be preserved, and the outcome aggregate will be as per the report's specifications.
