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.

 

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?

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • 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.