Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DISTINCTCOUNT with filters help

  • 1.  DISTINCTCOUNT with filters help

    Posted 13 days ago
    Hi. Newbie here who has spent far too much time trying to find an answer so any help will be very much appreciated.

    My aim is to count the number of unique Customer IDs in a table column. I can do this for an unfiltered column and I can do this for a filtered column but I'm struggling when I want to filter one column by one value and filter a separate column by two values.

    The table below is a simple version. The real table has a lot more columns.

    So, I want to count the unique 'Customer ID's for customers with either a 'blue' or a 'green' 'Product ID' who also have a Product Description of 'accept'. THe answer here should be 4 (everyone except customer 101).

    When I do this I always end up with 5.

    I've tried many variations of code but as way of an example here's one below. Any help or even just a point in the right direction would be great.

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]), 'Table' [Product Description="accept", 'Table' [Product ID]="blue" || 'Table' [Product ID]="green" 

    Customer ID Product ID Product Description Company
    100 blue accept ABC Co
    100 blue accept ABC Co
    100 red accept ABC Co
    100 green accept ABC Co
    101 red postpone DEF Co
    102 blue accept XYX Co
    102 red accept XYX Co
    103 blue accept ABC Co
    103 red accept ABC Co
    103 green accept ABC Co
    104 green decline DEF Co
    104 blue postpone DEF Co


    ------------------------------
    Tom Green
    ------------------------------


  • 2.  RE: DISTINCTCOUNT with filters help

    Top Contributor
    Posted 13 days ago
      |   view attached
    Hi Tom, try the following code - you were nearly there, but a couple of parenthesis out etc...

    I think your counting above is incorrect.  So you are looking for description = Accept AND (product is is Blue OR product is Green).  There are only 5 rows that match that criteria, and 3 unique Customer IDs.  I've highlighted the rows in the image below



     CALCULATE(
        DISTINCTCOUNT( 'Table'[Customer ID] ),
        'Table'[Product Description] = "accept"
            && ( 'Table'[Product ID] = "blue"
            || 'Table'[Product ID] = "green" )
    )

    attached is the file.

    ------------------------------
    Ben Howard, UK.

    Please mark any answer as recommended if it helps you.
    ------------------------------

    Attachment(s)

    pbix
    PBIUG.pbix   26 KB 1 version


  • 3.  RE: DISTINCTCOUNT with filters help

    Posted 13 days ago
    Thank you so much Ben. Really appreciate you taking the time to help me out. That does the trick. Thanks again.

    ------------------------------
    Tom Green
    ------------------------------