topic Re: How to do distinct count by time period with a DAX measure in DAX Commands and Tips

How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 06:04:56 GMT

I have a dimension table with column Account ID

Account ID

and fact table linked by Account ID

Merchant	Account ID	Date	Value
1	a	January	500
2	a	February	100
2	b	February	300
2	c	January	200
3	a	January	900
3	b	February	400

I am looking to create a measure that would be used in a table that would show for each merchant, how many accounts have used it in a selected time period (e.g. YTD)

so for February YTD it would show:

Merchant	Number of Accounts
1	1
2	3
3	2

but for January YTD it would show:

Merchant	Number of Accounts
1	1
2	1
3	1

I have thus far got the following:
YTD count = CALCULATE(COUNTROWS(FILTER(VALUES(Dim[Account ID]),CALCULATE(SUM(Fact[Value])>0),ALL('Dates'),DATESYTD('Dates'[Date]))

Unfortunately due to the high cardinality of the Merchant column in the dimension table and millions of records, it is exceeding the available resources.

Please could I be guided on how to make the measure more efficient

Re: How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 06:18:29 GMT

Hi jaineshp the function "EXISTS" doesn't appear to be available to use in a DAX measure in PowerBI. it has a red squiggle underneath it

Re: How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 06:52:15 GMT

Hi jaineshp this seems to give me the count of all accounts unfortunately

Re: How to do distinct count by time period with a DAX measure

mdaatifraza5556 — Tue, 19 Aug 2025 06:59:51 GMT

Hi aleary78

Can you please try the below dax to get your result ?

Create only one measure to count using below dax.

Accounts Count YTD =

CALCULATE (

DISTINCTCOUNT ( fact_table[Account ID] ),

DATESYTD ( 'dim_date'[Date] )

)

Result

When Feb is selected

When Jan is selected

If this answers your questions, kindly accept it as a solution and give kudos.

I have also attached the pbix file for your reference.

Re: How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 07:11:22 GMT

Hi mdaatifraza5556 this also gives me the total number of accounts which makes me think there is something wrong with my schema. I will see if I can determine. Having said that, my orignial formula worked when I selected a subset of merchants. Unfortunately I am not able to share pbix externally

Re: How to do distinct count by time period with a DAX measure

mdaatifraza5556 — Tue, 19 Aug 2025 07:14:53 GMT

Hi aleary78

You can refer the pbix file which i have shared.

try to make the schema as you can see in the pbix file.

Make 1 to * connection ( dim_date ---- fact table using date column)

Make 1 to * connection ( dim_id ---- fact table uisng acount_id )

Create month column in your dim_date table

If this does not work then please provide sample dataset which relates to your data.

If this answers your questions, kindly accept it as a solution and give kudos.

Re: How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 07:31:05 GMT

Hi mdaatifraza5556 I believe your answer is correct as your file shows this so happy to give kudos. Unfortunatley I am not able to share pbix. Can you see if you can spot anything visibily wrong with my schema below? The MCA Details table is the dimension table I was referring to and the CV table is the fact table.

Re: How to do distinct count by time period with a DAX measure

mdaatifraza5556 — Tue, 19 Aug 2025 09:01:49 GMT

Hi aleary78

Make sure that

there is no any filter or slicer is applied from the table which i have circile.

Please remove every filter or the slicer is applied from that table for testing purpose
and then check it.

Or Just remove every slicer and filter ( keep only for date table to select month)

Then try to validate.

Or if you are able to create some sample data exactily as your schema then i can try from my end.

If this answers your questions, kindly accept it as a solution and give kudos.

Re: How to do distinct count by time period with a DAX measure

aleary78 — Tue, 19 Aug 2025 12:17:05 GMT

Hi mdaatifraza5556 I have inputted my numbers into your file. It would appear that I need to store account ID in the fact table too and distinct count there. I believe this is to do with the structure of my databetween the CV table and the Tagging table in my image, but I need to keep that for other reasons. Thank you for your guidance. Should work perfectly now

Re: How to do distinct count by time period with a DAX measure

Natalie_iTalent — Fri, 05 Sep 2025 20:46:31 GMT

Hello aleary78, the comment you responded to has been removed.

Best,

Natalie H.

Community Manager

Re: How to do distinct count by time period with a DAX measure

Natalie_iTalent — Fri, 05 Sep 2025 20:48:48 GMT

Hello aleary78, the comment you responded to has been removed.

Best,

Natalie H.

Community Manager