cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MFester Frequent Visitor
Frequent Visitor

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 4
Highlighted
Super User
Super User

Re: Distinct counting with conditions

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

 

 

Re: Distinct counting with conditions

can you please try this and check if it is working

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

Re: Distinct counting with conditions

 

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

MFester Frequent Visitor
Frequent Visitor

Re: Distinct counting with conditions

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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 168 members 1,892 guests
Please welcome our newest community members: