I need help with Distinct Count with excluding filter

[TcT85]

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.

Distinct count = CALCULATE(DISTINCTCOUNT('Defect table'[Serialnr] ), 'Defect table'[Type] = "Defect" )

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

How can i make a similar DAX formula with excluding filter?

I would like to exclude warning from this formula.

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%

[v-achippa]

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

[v-achippa]

Hi @TcT85,

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

[TcT85]

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?

[mdaatifraza5556]

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.

[Cookistador · Accepted solution]

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]

@TcT85 , Try this

DAX

DistinctCountExcludingWarningAndBlanks =

CALCULATE(

    DISTINCTCOUNT('Defect table'[Serialnr]),

    'Defect table'[Type] <> "Warning",

    'Defect table'[Type] <> BLANK()

)

And then 

DAX

Yield =

1 - DIVIDE(

    [DistinctCountExcludingWarningAndBlanks],

    SUM('Produced Table'[Quantity])

)

[mdaatifraza5556]

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.

[Cookistador]

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()

    )

[bhanu_gautam]

@TcT85 , Create two measures

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

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