Calculate distinctcount exclude specific filter

[GeertK]

I have a problem which I can't seem to find the answer to. I think the best way to explain the problem I'm having is with an example. So this is an example of the dataset that I have, everything is in 1 table (PartsandQDs):

part_id		Loadpart		Fault
1		A
2		A		damaged
3		A		unpainted
4		B
5		B		damaged
6		B
7		C
8		C		damaged
9		D
10		D		unpainted
11		D		damaged
12		D		damaged
13		D
14		D
15		D

What I need is to create a measure that calculates the percentage of the fault based on different variable/filters. My original data has many variable, in the example I'm only using Loadpart and fault. This measure I use in different visuals one of which is a table which I'll be using in this example. 

When I filter on any variable that is NOT fault it works. It shows the data in the right way:

Filter:

Loadpart: D

Output:

Loadpart		fault		Total faults		total produced		%
D		unpainted		1		7		14%
D		damaged		2		7		29%
D				4		7		57%

However when I try to filter on 'Fault' it gives the following output:

Filter:

Fault: damaged

Output:

Loadpart		fault		Total faults		total produced		%
A		damaged		1		1		100%
B		damaged		1		1		100%
C		damaged		1		1		100%
D		damaged		2		2		100%

In this case the 'total produced' is not correct. The total produced measure should ignore the 'fault' filter while keeping all the other filters. What I want as an output is the following:

Filter:

Fault: damaged

Output:

Loadpart		fault		Total faults		total produced		%
A		damaged		1		3		33%
B		damaged		1		3		33%
C		damaged		1		2		50%
D		damaged		2		7		29%

The measures I have tried so far to calculate the 'total produced' which have all not given the right results are the following:

total produced = CALCULATE(DISTINCTCOUNT(PartsandQDs[part_id]),REMOVEFILTERS(PartsandQDs[fault]))

total produced = CALCULATE(DISTINCTCOUNT(PartsandQDs[part_id]),ALL(PartsandQDs[fault]))

The strange thing is that I have the exact same dashboard in Qliksense (I'm trying to move from Qliksense to PowerBI) and I use a variation of the measures above and it does work correctly.

I was wondering if anyone has a suggestion to what I could use to solve my problem.

Thanks in advanced!

[v-jianboli-msft]

Hi @GeertK ,

Approve with @ValtteriN , REMOVEFILTER function works fine in my side.

Here is another measure that may helps you, please try:

Measure = CALCULATE(DISTINCTCOUNT(PartsandQDs[part_id]),FILTER(ALL(PartsandQDs),[Loadpart]=MAX('PartsandQDs'[Loadpart])))

Final output:

[ValtteriN · Accepted solution]

Hi,

My first instinct was use REMOVEFILTERS excatly like you suggested and it worked:

I recommend checking the other filters that might affect your table

[GeertK]

Thanks a lot for your response! This confirms that the problem must be in my dashboard. Unfortunately I can't share my dashboard due to production sensitive information, but I decided to create my dashboard from scratch and check step for step where it goes wrong.