As mentioned in the last blog, to allow users to add new dimensions to the table without adding the context filter to the measure, we can use ALLEXCEPT function.

To summarize our requirement now, we want the measure we created to be only filtered by the state no matter what dimensions will be added to the table.  The ALLEXCEPT function is to remove all the filters applied except the state.

Another potential defect of this expression could be the external filter. In the following picture, you can find that if we filter the whole table using a slice card, the total percentage would not be 100%.

That is because the denominator in our expression simply excludes all the context filters no matter whether they are from outside or inside. So, what we want to achieve here is to exclude the filters from the table but keep the outside filter working.

To achieve this function, we need to modify our expression as shown in the following picture.

Now, we can see the measure we created is only influenced by the external filter.

That is all about the context filter function in Power bi.

If you have any questions,  feel free to reach out to me through the Link.

Tony Tan
Author: Tony Tan