Forum Discussion

MFester's avatar
MFester
Frequent Visitor
7 years ago

Distinct counting with conditions

Hello, 

 

Hopefully someone can help me.

 

I need to distinct count with a condition.

 

I have a list of sellerID, a list of products and a date.

 

Basically i want to know how many different sellers have sold X, Y and Z products. A sellerID can be the same if a seller sells different products. So I need a distinct count

 

I need a formula something like this CountDistinct(SellerID) if (Product) is 'banana' or 'apple'.

 

Is this possible?

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi MFester 

    Your question is a bit ambiguous. First you say you want an AND of the products sold and then an OR. Also, it would help if you provide some sample data and and example.

    If it's an OR:

    1. Place product in a slicer and select the ones you want

    2. Create a simple measure and place it in a card visual:

    Measure = DISTINCTCOUNT(Table1[sellerID])

    If it's an AND:

    1. Place product in a slicer and select the ones you want

    2. Create this measure and place it in a card visual:

     

    Measure2 =
VAR ProdsInFilter_ =
    DISTINCT ( Table1[Product] )
VAR AuxTable_ =
    FILTER (
        DISTINCT ( Table1[SellerID] ),
        VAR ProdsCurrentSeller_ = CALCULATETABLE ( DISTINCT ( Table1[Product] ) )
        RETURN
            COUNTROWS ( INTERSECT ( ProdsInFilter_, ProdsCurrentSeller_ ) ) = COUNTROWS ( ProdsInFilter_ )
    )
RETURN
    COUNTROWS ( AuxTable_ )

    Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

    Cheers  Datanaut

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    can you please try this and check if it is working

    Measure = CALCULATE(DISTINCTCOUNT('Table'[SellerId]),OR('Table'[Product] = "apple",'Table'[Product]="banana"))
    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

       

      That should work. It's equivalent to (although less versatile than) my first solution. You can check it yourself 

  • MFester's avatar
    MFester
    Frequent Visitor
    7 years ago
    SellerIDProductDate
    1Apple1.1.2019
    1Banana1.1.2020
    2Chair1.1.2021
    3Apple1.1.2022
    4Apple1.1.2023
    5Apple1.1.2024
    5Banana1.1.2025
    5Chair1.1.2026

     

    Here is an example of the data.

     

    I can't use a filter, because I need different groups in the same table/figure, therefor I think I need measures for each "group" i wanna create.


    For instance I wanna know how many sellers have sold fruit, have many sellers furniture and so forth.

    Because a seller can sell apple more than once i need distinct count.

    The "or" suggestion could only have two groups. "Banana" and "apple", but if we also add "Orange" it doesn't work I think.

     

    Thank you all for your helpful tips.