In order to address calculation errors or unfavorable outcomes stemming from filter context difficulties in DAX cubic measures, you can make use of the following techniques:
Learn the CALCULATE Function properly: The reason why you will rarely come across a DAX implementation without a CALCULATE statement is because DAX allows you to modify the filter context when CALCULATE is applied. That is, when using the EXCLUDE() Measure in the example above, one is able to state that the format of the EXCLUDE() function in CALCULATE([Measure], ALL(TableName)) is such that ALL filters on the variable TableName are removed, and the particular Measure is recalculated in the context of the specified variable.
Context Reset Using ALL or REMOVE FILTERS:
Beneath these circumstances, when would you want to filter some columns or some tables? Can ALL and REMOVE FILTERS be of assistance? If a measure is wrapped with ALL and a column or table indicating a filter presents that Measure, that column or table shall return measured results unfiltered by column or table. This is beneficial when trying to obtain accurate overall results in cases where usual row filters would have interfered with the outcome. Example: SUMX(ALL('Sales'), [Amount]).
Similar is the use of SUMX or AVERAGEX at Row Context: In cases where it is required to do row-wise iterations over an underlying data set, starting with calculating totals of certain values, then SUMX or AVERAGEX functions are appropriate. Those functions process rows one by one, performing some operations, and after processing all rows, perform the final operation. This use-case is beneficial in scenarios where total values are incorrect due to the overall measures being affected by row-level filters. For example, instead of using the regular measure sales[Qty] and sales[Price] by fetching values using foreign key join, we shall iterate every row using the formula SUMX(Sales, Sales[Quantity] * Sales[Price]).
Data model: In some instances, incorrect answers can be caused by some relationships in your model. Make sure that you have created all the links that are supposed to exist between the various tables in your data model, and consider when appropriate using RELATED or RELATED TABLE functions to obtain the ancillary information. Correct relationships will enhance the ability of the filters to move across the tables correctly.
