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

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.