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

[aleary78]

I have a dimension table with column Account ID

Account ID

a

b

c

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

[mdaatifraza5556 · Accepted solution]

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.

[aleary78]

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

[mdaatifraza5556]

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.

[aleary78]

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. 

[mdaatifraza5556]

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.

[aleary78]

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

[jaineshp]

Hey @aleary78,

YTD Accounts =
SUMX(
VALUES(Dim[Account ID]),
IF(
COUNTROWS(
FILTER(
Fact,
Fact[Value] > 0
&& Fact[Date] <= MAX('Dates'[Date])
&& YEAR(Fact[Date]) = YEAR(MAX('Dates'[Date]))
)
) > 0,
1,
0
)
)

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[aleary78]

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

[jaineshp]

Hey @aleary78,

Try this:

YTD Accounts =
SUMX(
VALUES(Dim[Account ID]),
IF(
EXISTS(
FILTER(
Fact,
Fact[Value] > 0 &&
Fact[Date] <= MAX('Dates'[Date]) &&
YEAR(Fact[Date]) = YEAR(MAX('Dates'[Date]))
)
),
1,
0
)
)

• Replaced FILTER(VALUES()) with SUMX pattern
• Used direct date range instead of DATESYTD
  
• Eliminated ALL() function that was removing filter context

This pattern handles millions of records efficiently and works with high cardinality merchant columns.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[aleary78]

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