Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

View all the Fabric Data Days sessions on demand. View schedule

Reply
Nelson-Mwangi
Regular Visitor

DAX measure to Calculate Distinct count for first-time listing for the selected period.

‎09-21-2025 03:53 AM

I need your help with a DAX measure to generate a count of distinct first-time listing each brand for the selected period.

 

My data model is in Power Pivot(I will appreciate a measure that will work in PowerBi & PowerPivot), and when I create a table with Month Name on the columns, each outlet should only be counted once (First time purchase) which is captured on the 'Listing Date" column.

The total rows and columns should reflect the overall distinct count.

The code in the image attached works (got help from Copilot), but I was wondering if there is an alternative that’s easier to follow. 

The relationship between the calendar table and Document Listing table is on "Date' column while the first listing date is on the listing date column.

I'm open to all solutions including the possibility of achieving the result with the date column.

Thank you.

image.pngimage.png

Message 1 of 12
1,112 Views
0
1 ACCEPTED SOLUTION
v-priyankata
Community Support
Community Support
In response to Nelson-Mwangi

‎10-06-2025 09:59 PM

Hi @Nelson-Mwangi 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Based on my understanding, could you please try the following DAX? It might be helpful.

First-Time Listings =
COUNTROWS(
    SUMMARIZE(
        'Document Listing',
        'Document Listing'[Customer Code],
        'Document Listing'[Brand],
        "FirstListDate", CALCULATE(MIN('Document Listing'[ListingDate]))
    )
)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Thanks.

View solution in original post

Message 7 of 12
760 Views
11 REPLIES 11
Praful_Potphode
Solution Sage
Solution Sage

‎11-05-2025 07:17 AM

Hi @Nelson-Mwangi ,

 

please try below measure:

Distinct First Listing Brand Count =
COUNTROWS(
    -- 1. Summarize to find the minimum (first) ListingDate for every Brand
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('Document Listing'[Brand]),
            "@FirstListingDate", MIN('Document Listing'[ListingDate])
        ),
        -- 2. Ensure that the calculation respects the date filter currently applied
        -- This step is critical because 'ListingDate' is not the active relationship
        ALLSELECTED('Calendar'[Date])
    )
    -- 3. Filter the resulting table: only keep rows where the Brand's first listing 
    -- date falls within the dates currently visible in the visual (e.g., the specific month)
    FILTER(
        KEEPFILTERS(VALUES('Calendar'[Date])),
        'Document Listing'[@FirstListingDate] >= MIN('Calendar'[Date]) &&
        'Document Listing'[@FirstListingDate] <= MAX('Calendar'[Date])
    )
)

 

Please give kudos or mark it as solution once confirmed.

 

Thanks and Regards,

Praful

Message 12 of 12
394 Views
0
Nelson-Mwangi
Regular Visitor

‎09-21-2025 07:08 PM

Unfortunately, this is my first post I'm not allowed to attach files.

The snip below has a sample data  and the relationship - Hope that helps.

 

image.png


image.png

Message 3 of 12
1,024 Views
0
tayloramy
Community Champion
Community Champion
In response to Nelson-Mwangi

‎09-22-2025 06:32 AM

Hi @Nelson-Mwangi, 

 

Please see this post for how to provide sample data: 
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

 

Also take a look at this post: 
(1) How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this post as the solution. 

Message 4 of 12
1,012 Views
Nelson-Mwangi
Regular Visitor
In response to tayloramy

‎09-27-2025 10:28 AM
Customer CodeCustomer NameBrandListingDateProduct CodeSalesmanDateSales
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636328Kitale Counter01/07/20250.17
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636328Titus Peter01/08/20250.17
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636349Janet Cheruiyot01/08/20250.17
KE0138196Swam W&S,KitaleDon Julio01/07/2025636349Titus Peter01/07/20250.5
KE0141961Bonile wines ktlDon Julio01/07/2025636328Kitale Counter01/07/20250.33
KE0141961Bonile wines ktlDon Julio01/07/2025636350Kitale Counter01/07/20250.17
KE0149467Lion Liquor Store, KibometDon Julio01/08/2025636328Kitale Counter01/08/20250.17
KE0152923Kitale Club,Kitale TownDon Julio01/08/2025636349Kitale Counter01/08/20250.17
KE0152923Kitale Club,Kitale TownDon Julio01/08/2025636328Dennis Memba01/09/20250.33
KE0156529Alaska Bar,SibangaDon Julio01/08/2025636349Janet Cheruiyot01/08/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Kitale Counter01/08/20250.08
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/07/20250.08
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/07/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/08/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025763807Jasron Anyasi01/08/20250.12
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/09/20250.17
Message 6 of 12
826 Views
0
v-priyankata
Community Support
Community Support
In response to Nelson-Mwangi

‎10-06-2025 09:59 PM

Hi @Nelson-Mwangi 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Based on my understanding, could you please try the following DAX? It might be helpful.

First-Time Listings =
COUNTROWS(
    SUMMARIZE(
        'Document Listing',
        'Document Listing'[Customer Code],
        'Document Listing'[Brand],
        "FirstListDate", CALCULATE(MIN('Document Listing'[ListingDate]))
    )
)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Thanks.

Message 7 of 12
761 Views
Nelson-Mwangi
Regular Visitor
In response to v-priyankata

‎10-13-2025 04:07 AM
First-Time ListingsCalcColumn Labels   
Row LabelsJulyAugustSeptemberGrand Total
Bulleit Bourbon3  3
Casamigos 213
Don Julio74110
Gordons Dry Gin2324942
JW Black Label10110480141
JW Blonde20351659
JW Gold Reserve3  3
JW Red Label1009276137
Singleton1518427
Tanq Ten21 3
Tanqueray LDG37210
Grand Total277287189438

 

Sorry for the delayed response. The measure returns the correct totals but monthly count is not unique.

Message 9 of 12
693 Views
0
v-priyankata
Community Support
Community Support
In response to Nelson-Mwangi

‎11-05-2025 01:08 AM

Hi @Nelson-Mwangi 
Sorry for delayed response, please try below DAX.

First-Time Listings =
VAR SelectedDates =
   VALUES ( 'Calendar'[Date] )
RETURN
CALCULATE (
   DISTINCTCOUNT ( 'Document Listing'[Customer Code] & "-" & 'Document Listing'[Brand] ),
   FILTER (
       'Document Listing',
       'Document Listing'[ListingDate]
           = CALCULATE (
               MIN ( 'Document Listing'[ListingDate] ),
               ALLEXCEPT (
                   'Document Listing',
                   'Document Listing'[Customer Code],
                   'Document Listing'[Brand]
               )
           )
   ),
   TREATAS ( SelectedDates, 'Document Listing'[ListingDate] )
)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Thanks.

Message 10 of 12
403 Views
v-priyankata
Community Support
Community Support
In response to v-priyankata

‎11-10-2025 01:11 AM

Hi @Nelson-Mwangi 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

Message 11 of 12
282 Views
0
v-priyankata
Community Support
Community Support
In response to v-priyankata

‎10-10-2025 02:10 AM

Hi @Nelson-Mwangi 

Thank you for reaching out to the Microsoft Fabric Forum Community.

 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

 

Message 8 of 12
728 Views
0
Nelson-Mwangi
Regular Visitor
In response to tayloramy

‎09-27-2025 10:22 AM
Customer CodeCustomer NameBrandListingDateProduct CodeSalesmanDateSales
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636328Kitale Counter01/07/20250.17
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636328Titus Peter01/08/20250.17
KE0064975Fenix Liquors Store, KitaleDon Julio01/07/2025636349Janet Cheruiyot01/08/20250.17
KE0138196Swam W&S,KitaleDon Julio01/07/2025636349Titus Peter01/07/20250.5
KE0141961Bonile wines ktlDon Julio01/07/2025636328Kitale Counter01/07/20250.33
KE0141961Bonile wines ktlDon Julio01/07/2025636350Kitale Counter01/07/20250.17
KE0149467Lion Liquor Store, KibometDon Julio01/08/2025636328Kitale Counter01/08/20250.17
KE0152923Kitale Club,Kitale TownDon Julio01/08/2025636349Kitale Counter01/08/20250.17
KE0152923Kitale Club,Kitale TownDon Julio01/08/2025636328Dennis Memba01/09/20250.33
KE0156529Alaska Bar,SibangaDon Julio01/08/2025636349Janet Cheruiyot01/08/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Kitale Counter01/08/20250.08
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/07/20250.08
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/07/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/08/20250.17
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025763807Jasron Anyasi01/08/20250.12
KE0034887Sirwo Resort, KapcheropGordons Dry Gin01/07/2025716210Jasron Anyasi01/09/20250.17
Message 5 of 12
861 Views
0
tayloramy
Community Champion
Community Champion

‎09-21-2025 09:15 AM

Hi @Nelson-Mwangi, 

 

Can you please provide sample data in a usable format (not a screenshot)?

 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

Message 2 of 12
1,065 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All