How to Optimize this DAX to Count Distinct Date by User

[aidelapplicate]

Hello,

I have this DAX, which is used to count how many days the user failed. But the issue is, this DAX ran very slow and causes "Theres not enough memory to complete this operation" error.

Count Failed =
CALCULATE(DISTINCTCOUNT(Table[date],
FILTER(SUMMARIZE(Table, Table[user], Table[date], "Failed", [Failed Count]), [Failed Count] <> BLANK()))

 Failed Count measure is a measure i use to count how many user failed in the category, with some conditions like targets.

The Table currently has 5million rows, and i know the FILTER function causes the performance of the visual to be slow.

Is there a way that i can optimize this DAX?

[MBernalBI]

Hi @aidelapplicate ,

Could you attach an example of the table that you work it and the DAX code of the measure Failed Count?

Thanks!

[aidelapplicate]

Hello,

My table looks like this

and the target for sales is 100.

Here is my code for Failed Count somewhat looks like this

Failed Count =
Calculate(DISTINCTCOUNT(Table[User]), FILTER(SUMMARIZE(Table, Table[User], SUM(Sales) =< 100)))

 This DAX also i think can be optimized more to avoid the use of FILTER and summarize the whole 5mil row table

[v-xinruzhu-msft]

Hi @aidelapplicate 

You can consider to create two measures

e.g 

Failed_sum = CALSULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[User]))
Failed_Count=CALCULATE(DISTINCTCOUNT(Table[User]),[Failed_sum ]<=50)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.