Calculate distinctcount exclude specific filter

GeertK — Fri, 26 Aug 2022 08:12:42 GMT

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!

Re: Calculate distinctcount exclude specific filter

ValtteriN — Fri, 26 Aug 2022 08:59:42 GMT

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

Re: Calculate distinctcount exclude specific filter

v-jianboli-msft — Wed, 31 Aug 2022 07:01:50 GMT

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:

Re: Calculate distinctcount exclude specific filter

GeertK — Wed, 31 Aug 2022 10:42:54 GMT

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.

topic Re: Calculate distinctcount exclude specific filter in DAX Commands and Tips

Calculate distinctcount exclude specific filter

Re: Calculate distinctcount exclude specific filter

Re: Calculate distinctcount exclude specific filter

Re: Calculate distinctcount exclude specific filter