Forum Discussion

TcT85's avatar
TcT85
Helper III
3 years ago
Solved

Distinct count with multiple filters

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

BarcodeOperatorJudgementOperatorNgType
1678564NgAcceptable
1678564NgSolder Bridge
1678565FalseCall 
1678565NgAcceptable
1678565NgSolder Bridge
1678566FalseCall 
1678566NgAcceptable
1678566NgSolder Bridge
1678570NgAcceptable
1678570NgSolder Bridge
1678571NgAcceptable
1678571NgSolder Bridge
1678572NgAcceptable
1678572NgPin Missing
1678572NgSolder Bridge
1678574NgAcceptable
1678574NgSolder Bridge
1678574FalseCall 
1678574NgAcceptable
1678574NgInsufficient Solder
1678574NgSolder Bridge
1678575NgAcceptable
1678575NgSolder Bridge
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

10 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
    MahyarTF
    Memorable Member
    3 years ago

    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's avatar
      TcT85
      Helper III
      3 years ago

      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's avatar
        MahyarTF
        Memorable Member
        3 years ago

        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

  • mario_enrique's avatar
    mario_enrique
    Regular Visitor
    2 years ago

    un ejemplo:

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