DISTINCTCOUNT taking up too much memory

[Anonymous]

Hi, I have a distinctcount calculated column on a summarized table "Summary" which is taking too long to compute (my computer runs out of memory before it can finish). Please can someone help me find a way to make this work more efficiently? Thank you.

DISTINCTCOUNT_USERS =

calculate(distinctcount(Table1[UserID]),

filter(Table1,Table1[Code]=Summary[Code]),

filter(Table2,Table2[Status]="Completed"),

filter(Table2,Table2[EventDate]=Summary[Date]))

(Table1 and Table2 are related)

[amitchandak]

@Anonymous ,
Try if this can work better

countx(summarize(calculatetable(Table1,
filter(Table1,Table1[Code]=Summary[Code]),
filter(Table2,Table2[Status]="Completed"),
filter(Table2,Table2[EventDate]=Summary[Date])),Table1[UserID])[UserID])

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use TREATAS as below if it's still too slow, consider moving the calculation to Power Query.

DISTINCTCOUNT_USERS_ = 
VAR __filter = { ( Summary[Code], Summary[Date], "Completed" ) }
RETURN 
CALCULATE(
    DISTINCTCOUNT( Table1[UserID] ),
    TREATAS( 
        __filter, 
        Table1[Code], Table2[EventDate], Table2[Status]
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @amitchandak are you sure the brackets etc. are in the right place? Power BI doesn't like that code

[amitchandak]

@Anonymous  one , was missing

countx(summarize(calculatetable(Table1,
filter(Table1,Table1[Code]=Summary[Code]),
filter(Table2,Table2[Status]="Completed"),
filter(Table2,Table2[EventDate]=Summary[Date])),Table1[UserID]),[UserID])