REMOVEFILTERS not doing what I expected in Power BI

user_4919511 0
Viewed 1925

Hi I have the following data and am trying to return a distinctcount of the number of Teams irrespective of what teams are filtered to, but taking into account the filters applied to Client.

Team Client Data

i'm using the following DAX

Total Team Count = CALCULATE(DISTINCTCOUNT(Sheet1[Team]),REMOVEFILTERS(Sheet1[Team]))

I've filtered my data to Client = aa and bb so am expecting Total Team Count = 4 (a, b, c & g)

this works fine when I filter to Team = a or b However c & d return a Total Team Count = 3 it seems filtering to c returns a count of a, b & c whilst d returns a, b, g. So it seems to be returning a distinctcount of the Teams with a common Client to the Team filtered to.

What am I doing wrong? How do I get it to return Total Team Count = 4 irrespective of the Team filter?

dax powerbi
2 Answers

This is a case of auto-exist run amok. Please read the linked article.

In your case, when you filter down to Team c, the only remaining Client is aa, which means despite having Client bb selected too, the Client column has been indirectly filtered out by Team c even though you later try to remove that filter from Team in your measure.

As mentioned in the article, the solution is to build a star schema that breaks out dimensions into their own tables.

user_7652260

Instead of using remove filters use allexcept. You can use the following dax formula:

Total Team Count = 
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Team]), 
    ALLEXCEPT('Sheet1', 'Sheet1'[Team], 'Sheet1'[Client])
)
user_131127580
Related