Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to Optimize this DAX to Count Distinct Date by User

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.   Co...
Need Help
Show and Tell
Tips and Tricks
MBernalBI's avatar
MBernalBI
Frequent Visitor
3 years ago

Hi Anonymous ,

 

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

 

Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous 

      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.