Forum Discussion
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
- ValtteriNCommunity 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- GeertKNew 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.
- v-jianboli-msftCommunity Support