Forum Discussion

TcT85's avatar
TcT85
Helper III
1 year ago
Solved

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 incl...
Need Help
  • Cookistador's avatar
    Cookistador
    1 year ago

    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] <> ""
            )
        )
    RETURN
        IF(
            TotalProduced > 0,
            1 - DIVIDE(DistinctDefects, TotalProduced),
            BLANK()
        )
    Because due to my test, the value was empty and not blank
    If it is not working, you have to replace
    && 'Defect Table'[Type] <> ""
    By
    && 'Defect Table'[Type] <>BLANK()
     
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

TcT85 , Create two measures

DAX
DistinctCountExcludingWarning =
CALCULATE(
DISTINCTCOUNT('Defect table'[Serialnr]),
'Defect table'[Type] <> "Warning"
)

 

Yield =
1 - DIVIDE(
[DistinctCountExcludingWarning],
SUM('Produced Table'[Quantity])
)