Issue with DistinctCount Custom Column Calculation

[vin26]

Hello,

I have huge data table (more than 50 million rows) on which I have created below Column and measure. Column Calculation works fine when I use COUNT but not working when I use DISTINCTCOUNT, it just showing 'Working on it..". Could you please someone help me with alternative to the column calculation or replicate column calculation in measure.

Sample Table:

Month	EMPID	VisitID
Mar-24	A111	V123
Mar-24	A111	V123
Mar-24	A111	V345
Mar-24	A111	V345
Mar-24	A111	V222
Mar-24	A111	V333
Mar-24	A222	V56
Mar-24	A222	V678
Mar-24	A222	V456
Mar-24	A222	V456
Mar-24	A222	V444
Mar-24	A333	V696
Mar-24	A333	V686
Mar-24	A333	V900
Mar-24	A333	V900
Mar-24	A333	V222
Mar-24	A333	V234
Mar-24	A333	V234

Custom Column:

Column = Var NoOfOccurrences = CALCULATE (
    DISTINCTCOUNT( Table1[VisitID]),
    FILTER (
        Table1,
        Table1[VisitID] = EARLIER ( Table1[VisitID] ) &&
        Table1[Month] = EARLIER ( Table1[Month] ) &&
        Table1[EMPID] = EARLIER( Table1[EMPID])
    )
)
RETURN
IF(NoOfOccurrences >= 2, "A", IF(NoOfOccurrences = 1, "B", BLANK()))

Measure:

Measure =CALCULATE( DISTINCTCOUNT (Table1[EMPID]), FILTER(Table1, Table1[Column] = “A”))

[rajendraongole1 · Accepted solution]

Hi @vin26 -Instead of creating a calculated column, it's better to replicate the logic in a measure, as measures are calculated dynamically and can handle large datasets more efficiently than columns.

MeasureColumnLogic =
VAR NoOfOccurrences =
CALCULATE(
DISTINCTCOUNT(Table1[VisitID]),
ALLEXCEPT(Table1, Table1[Month], Table1[EMPID])
)
RETURN
IF(NoOfOccurrences >= 2, "A", IF(NoOfOccurrences = 1, "B", BLANK()))

You can now use this measure in your final calculation with the above combination.

Measure =
CALCULATE(
DISTINCTCOUNT(Table1[EMPID]),
FILTER(
Table1,
[MeasureColumnLogic] = "A"
)
)

Hope this works in your scenerio