CODEWITHSUNDEEP
powerbidax
Oliver Humphreys
Oliver Humphreys

Reputation: 472

REMOVEFILTERS not doing what I expected in Power BI

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?

Upvotes: 5

Views: 4470

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 7

Agustin Palacios
Agustin Palacios

Reputation: 1206

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])
)

Upvotes: 0

Related Questions