Forum Discussion

GeertK's avatar
GeertK
New Member
4 years ago
Solved

Calculate distinctcount exclude specific filter

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!

  • 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

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion

    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's avatar
      GeertK
      New Member

      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.