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...
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.

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

      • TcT85's avatar
        TcT85
        Helper III
        3 years ago

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

         

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