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

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?

  • paladin21's avatar
    paladin21
    Regular Visitor
    3 years ago

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

      Hi paladin21,

       

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

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    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.