Distinct count with multiple filters

[TcT85]

Hi

I been trying different solutions for this topic but I can't get it to work.

I need to distinct count the barcode with 2 filters and that is "FalseCall" from OperatorJudgement and "Acceptable" from OperatorNgType

Here is the table

Barcode	OperatorJudgement	OperatorNgType
1678564	Ng	Acceptable
1678564	Ng	Solder Bridge
1678565	FalseCall
1678565	Ng	Acceptable
1678565	Ng	Solder Bridge
1678566	FalseCall
1678566	Ng	Acceptable
1678566	Ng	Solder Bridge
1678570	Ng	Acceptable
1678570	Ng	Solder Bridge
1678571	Ng	Acceptable
1678571	Ng	Solder Bridge
1678572	Ng	Acceptable
1678572	Ng	Pin Missing
1678572	Ng	Solder Bridge
1678574	Ng	Acceptable
1678574	Ng	Solder Bridge
1678574	FalseCall
1678574	Ng	Acceptable
1678574	Ng	Insufficient Solder
1678574	Ng	Solder Bridge
1678575	Ng	Acceptable
1678575	Ng	Solder Bridge

[mario_enrique]

un ejemplo:

= CALCULATE(DISTINCTCOUNT('Estado de OT'[Orden]),FILTER( 'Estado de OT','Estado de OT'[Estado De OT]<> "Cerrada"))

[DimaMD]

HI @TcT85 Is this your desired outcome?

[v-jianboli-msft · Accepted solution]

Hi @TcT85 ,

When the DISTINCTCOUNT() function finds no rows to count, it returns a BLANK, otherwise it returns the count of distinct values.

So if you want the both filter to be true and not return blank value, please try:

Distinctcount = CALCULATE( DISTINCTCOUNT('SPC DATA'[Barcode]),'SPC DATA'[OperatorJudgement] = "falsecall", 'SPC DATA'[OperatorNgType] = "Acceptable")+0

Output:

If you just want to calculate the barcode with "FalseCall" and "Acceptable" instead of requiring both values on the same line, please try:

Distinctcount2 = 
VAR _a =
    SUMMARIZE (
        'SPC DATA',
        [Barcode],
        "Flag",
            IF (
                "FalseCall"
                    IN SELECTCOLUMNS (
                        FILTER ( 'SPC DATA', [Barcode] = 'SPC DATA'[Barcode] ),
                        "OperationJudgement", [OperatorJudgement]
                    )
                        && "Acceptable"
                            IN SELECTCOLUMNS (
                                FILTER ( 'SPC DATA', [Barcode] = 'SPC DATA'[Barcode] ),
                                "OperationNgType", [OperatorNgType]
                            ),
                1,
                0
            )
    )
RETURN
    SUMX ( _a, [Flag] )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MahyarTF]

Hi,

Not sure what you mean ?

But I posted the below code :

DistinctCount =
                --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorJudgement] = "falsecall")

                --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorNgType] = "Acceptable")

                CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorJudgement] = "falsecall", Sheet48[OperatorNgType] = "Acceptable")

First Line if for calculating the distinct count of the FalseCall value

First Line if for calculating the distinct count of the Acceptable value

First Line if for calculating the distinct count for the rows that has both value in particular columns

Appreciate your kudos and please mark it as a solution if it helps you

[TcT85]

Hi MayharTF,

I used this one as well:

Distinctcount = CALCULATE( DISTINCTCOUNT('SPC DATA'[Barcode]), 'SPC DATA'[OperatorJudgement] = "falsecall", 'SPC DATA'[OperatorNgType] = "Acceptable")

What happens is that i get no data shown.

But if I use these with 1 single filter it works.

--CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorJudgement] = "falsecall")

 --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorNgType] = "Acceptable")

Im not sure if the blanks in the OperatorNgType mess up the data.

[MahyarTF]

The shown result is True,

Because in your sample data there are not any rows with FalseCall and Acceptable values (I mean together).

Would you please ask you give your Kudos and select it as a solution if it helps you

[TcT85]

Hmm so I need to replace the empty cells with values?

Or is there a way to make the DAX formula to understand this?

[MahyarTF]

Hi @TcT85 ,

You have two choices:

1- fill the empty cells with "Acceptable" value and run the existing DAX code.

2- use the below code for creating the Measure :

DistinctCount = --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorJudgement] = "falsecall")

                --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorNgType] = "Acceptable")

                --CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]), Sheet48[OperatorJudgement] = "falsecall", Sheet48[OperatorNgType] = "Acceptable")

                CALCULATE( DISTINCTCOUNT(Sheet48[Barcode]),

                           and(Sheet48[OperatorJudgement] = "falsecall",

                               or(Sheet48[OperatorNgType] = "Acceptable", ISBLANK(Sheet48[OperatorNgType]) )

                               )

                        )

It means if there is any record that the "OperatorJudgement" column is equal "Falsecall" and the "OperatorNgType" column is equal "Acceptable" or is null, this row is counted.

Appreciate your Kudos,

Please mark it as a solution if it helps you.

[paladin21]

It depends on the logic you want it to have. What you have posted acts when both statements are true ("FalseCall" AND "Acceptable"). If you want to show if either of them is true, you should change the code to reflect so like this:

Distinctcount = CALCULATE( DISTINCTCOUNT('SPC DATA'[Barcode]), ('SPC DATA'[OperatorJudgement] = "falsecall") || ('SPC DATA'[OperatorNgType] = "Acceptable"))

[TcT85]

Hi paladin21,

Understood but I need both to be true, otherwise I will get the wrong count.