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()
     
Cookistador's avatar
Cookistador
Super User
1 year ago

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"
        )
    )
RETURN
    IF(
        TotalProduced > 0,
        1 - DIVIDE(DistinctDefects, TotalProduced),
        BLANK()
    )