Distinct counting with conditions

[MFester]

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?

[MFester]

SellerID	Product	Date
1	Apple	1.1.2019
1	Banana	1.1.2020
2	Chair	1.1.2021
3	Apple	1.1.2022
4	Apple	1.1.2023
5	Apple	1.1.2024
5	Banana	1.1.2025
5	Chair	1.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.

[Anonymous]

can you please try this and check if it is working

Measure = CALCULATE(DISTINCTCOUNT('Table'[SellerId]),OR('Table'[Product] = "apple",'Table'[Product]="banana"))

[AlB]

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

[AlB]

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