Forum Discussion
I need help with Distinct Count with excluding filter
Hi,
I have two tables, and I need to find the yield for each type.
The tables are fictional, the real table contains over 25 different names in the Type Column.
I only know how to use with including with filters.
| Defect table | |||
| Product | Serialnr | Part | Type |
| A | 0001 | Corn | Defect |
| A | 0001 | Corn | Warning |
| A | 0002 | Carrot | Warning |
| A | 0002 | Corn | Defect |
| A | 0002 | Corn | Defect |
| A | 0002 | Corn | Scratch |
| A | 0003 | Carrot | Defect |
| A | 0004 | Cabbage | Defect |
| A | 0004 | Cabbage | Warning |
| B | 0006 | Carrot | Defect |
| B | 0007 | Corn | Warning |
| B | 0008 | Carrot | Warning |
| B | 0009 | Corn | Defect |
| B | 0010 | Carrot | Defect |
| B | 0011 | Carrot | Defect |
| Produced Table | ||||
| Product | Quantity | Defect | Warning | Scratch |
| A | 4 | 4 | 3 | 1 |
| B | 6 | 4 | 2 | 0 |
This is the result I want.
| Yield Table | |
| Product | Yield |
| A | 0% |
| B | 33% |
Hi TcT85
I included blank value in my table and now I get the following result
To fix that, I have to add the modify my formula with:
Yield =VAR TotalProduced =SUM('Produced Table'[Quantity])VAR DistinctDefects =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),FILTER('Defect table','Defect table'[Type] <> "Warning"&& 'Defect Table'[Type] <> ""))RETURNIF(TotalProduced > 0,1 - DIVIDE(DistinctDefects, TotalProduced),BLANK())
Because due to my test, the value was empty and not blankIf it is not working, you have to replace&& 'Defect Table'[Type] <> ""By&& 'Defect Table'[Type] <>BLANK()
9 Replies
TcT85 , Create two measures
DAX
DistinctCountExcludingWarning =
CALCULATE(
DISTINCTCOUNT('Defect table'[Serialnr]),
'Defect table'[Type] <> "Warning"
)Yield =
1 - DIVIDE(
[DistinctCountExcludingWarning],
SUM('Produced Table'[Quantity])
)Hi TcT85
The following measure will return the right result
Yield =VAR TotalProduced =SUM('Produced Table'[Quantity])VAR DistinctDefects =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),FILTER('Defect table','Defect table'[Type] <> "Warning"))RETURNIF(TotalProduced > 0,1 - DIVIDE(DistinctDefects, TotalProduced),BLANK())Hi TcT85
Please follow the below steps
1. Create measures using below DAX.
Distinct Defect Count (Excl Warning) =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),FILTER('Defect table','Defect table'[Type] <> "Warning"))2. Create second measures using below DAX.
Distinct Defect Count (Excl Warning) =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),FILTER('Defect table','Defect table'[Type] <> "Warning"))3. Here is result.
If this answers your question, kindly accept it as a solution and give kudos.Hi bhanu_gautam , mdaatifraza5556 and mdaatifraza5556
Thanks for the quick response all of your solution works, but I have an issue that even blank cells from Type column are included in the calculation.
Can anyone of you help me with a dax formula that also exclude the blanks?
TcT85 , Try this
DAXDistinctCountExcludingWarningAndBlanks =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),'Defect table'[Type] <> "Warning",'Defect table'[Type] <> BLANK())And thenDAXYield =1 - DIVIDE([DistinctCountExcludingWarningAndBlanks],SUM('Produced Table'[Quantity]))Hi TcT85
I included blank value in my table and now I get the following result
To fix that, I have to add the modify my formula with:
Yield =VAR TotalProduced =SUM('Produced Table'[Quantity])VAR DistinctDefects =CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr]),FILTER('Defect table','Defect table'[Type] <> "Warning"&& 'Defect Table'[Type] <> ""))RETURNIF(TotalProduced > 0,1 - DIVIDE(DistinctDefects, TotalProduced),BLANK())
Because due to my test, the value was empty and not blankIf it is not working, you have to replace&& 'Defect Table'[Type] <> ""By&& 'Defect Table'[Type] <>BLANK()Hi TcT85
Include one more filtering part in the filter.'Defect table'[Type] <> blank()
If this answers your questions, kindly accept it as a solution.
Hi TcT85,
Thank you for reaching out to Microsoft Fabric Community.
Thank you bhanu_gautam, mdaatifraza5556, Cookistador for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user's resolved your issue?
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
