topic Re: DAX measure to Calculate Distinct count for first-time listing for the selected period. in DAX Commands and Tips

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

Nelson-Mwangi — Sun, 21 Sep 2025 10:53:29 GMT

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.

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

tayloramy — Sun, 21 Sep 2025 16:15:55 GMT

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.

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

Nelson-Mwangi — Mon, 22 Sep 2025 02:08:17 GMT

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.

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

tayloramy — Mon, 22 Sep 2025 13:32:06 GMT

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.

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

Nelson-Mwangi — Sat, 27 Sep 2025 17:22:47 GMT

Customer Code	Customer Name	Brand	ListingDate	Product Code	Salesman	Date	Sales
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636328	Kitale Counter	01/07/2025	0.17
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636328	Titus Peter	01/08/2025	0.17
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636349	Janet Cheruiyot	01/08/2025	0.17
KE0138196	Swam W&S,Kitale	Don Julio	01/07/2025	636349	Titus Peter	01/07/2025	0.5
KE0141961	Bonile wines ktl	Don Julio	01/07/2025	636328	Kitale Counter	01/07/2025	0.33
KE0141961	Bonile wines ktl	Don Julio	01/07/2025	636350	Kitale Counter	01/07/2025	0.17
KE0149467	Lion Liquor Store, Kibomet	Don Julio	01/08/2025	636328	Kitale Counter	01/08/2025	0.17
KE0152923	Kitale Club,Kitale Town	Don Julio	01/08/2025	636349	Kitale Counter	01/08/2025	0.17
KE0152923	Kitale Club,Kitale Town	Don Julio	01/08/2025	636328	Dennis Memba	01/09/2025	0.33
KE0156529	Alaska Bar,Sibanga	Don Julio	01/08/2025	636349	Janet Cheruiyot	01/08/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Kitale Counter	01/08/2025	0.08
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/07/2025	0.08
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/07/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/08/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	763807	Jasron Anyasi	01/08/2025	0.12
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/09/2025	0.17

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

Nelson-Mwangi — Sat, 27 Sep 2025 17:28:32 GMT

Customer Code	Customer Name	Brand	ListingDate	Product Code	Salesman	Date	Sales
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636328	Kitale Counter	01/07/2025	0.17
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636328	Titus Peter	01/08/2025	0.17
KE0064975	Fenix Liquors Store, Kitale	Don Julio	01/07/2025	636349	Janet Cheruiyot	01/08/2025	0.17
KE0138196	Swam W&S,Kitale	Don Julio	01/07/2025	636349	Titus Peter	01/07/2025	0.5
KE0141961	Bonile wines ktl	Don Julio	01/07/2025	636328	Kitale Counter	01/07/2025	0.33
KE0141961	Bonile wines ktl	Don Julio	01/07/2025	636350	Kitale Counter	01/07/2025	0.17
KE0149467	Lion Liquor Store, Kibomet	Don Julio	01/08/2025	636328	Kitale Counter	01/08/2025	0.17
KE0152923	Kitale Club,Kitale Town	Don Julio	01/08/2025	636349	Kitale Counter	01/08/2025	0.17
KE0152923	Kitale Club,Kitale Town	Don Julio	01/08/2025	636328	Dennis Memba	01/09/2025	0.33
KE0156529	Alaska Bar,Sibanga	Don Julio	01/08/2025	636349	Janet Cheruiyot	01/08/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Kitale Counter	01/08/2025	0.08
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/07/2025	0.08
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/07/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/08/2025	0.17
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	763807	Jasron Anyasi	01/08/2025	0.12
KE0034887	Sirwo Resort, Kapcherop	Gordons Dry Gin	01/07/2025	716210	Jasron Anyasi	01/09/2025	0.17

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

v-priyankata — Tue, 07 Oct 2025 04:59:10 GMT

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.

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

v-priyankata — Fri, 10 Oct 2025 09:10:50 GMT

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.

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

Nelson-Mwangi — Mon, 13 Oct 2025 11:07:02 GMT

First-Time ListingsCalc	Column Labels
Row Labels	July	August	September	Grand Total
Bulleit Bourbon	3			3
Casamigos		2	1	3
Don Julio	7	4	1	10
Gordons Dry Gin	23	24	9	42
JW Black Label	101	104	80	141
JW Blonde	20	35	16	59
JW Gold Reserve	3			3
JW Red Label	100	92	76	137
Singleton	15	18	4	27
Tanq Ten	2	1		3
Tanqueray LDG	3	7	2	10
Grand Total	277	287	189	438

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

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

v-priyankata — Wed, 05 Nov 2025 09:08:25 GMT

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.

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

Praful_Potphode — Wed, 05 Nov 2025 15:17:45 GMT

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

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

v-priyankata — Mon, 10 Nov 2025 09:11:09 GMT

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.