Forum Discussion

BM_'s avatar
BM_
Frequent Visitor
6 years ago

Calculated Column very slow performance

Hi, I have created a calculated measure that performs very slowly. Maybe someone with more powerbi skills can help out. I have two tables (1) membership, and (2) visits. I want to know the per membership the number of visits in the year before the enddate of a membership. So I created a calculated column for this, but it takes >5 minutes to load.
 
#records memberships = 200.000
#records visits = 1M
 
So I created this formula, that says count the number of visits, for their last year, for memberships that have an enddate. But maybe I can do this much more efficiently.
 
This column I created in the membership table, so I have this value for all memberships.
I also created a column, Enddate minus 1YR
 
Visits_Enddate_Minus1YR = IF(NOT(ISBLANK([Enddate]));CALCULATE(COUNTA(Visits[MembershipID]);FILTER(Visits;Visits[MembershipID]=EARLIER(Memberships[MembershipID]));FILTER(Visits;Visits[Date].[Date]>=Memberships[Enddate minus 1YR].[Date])))
 
 
Need Help

1 Reply

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, BM_ 

     

    The slow performance may not only caused by your calculated column. Many factors may result in the slow performance, such as complex data model or the complex dax formula. You may try the following calculated column to see if it helps.

     

    IF(
           NOT(ISBLANK([Enddate])),
           var _id = Memberships[MembershipID]
           return
           CALCULATE(
               DISTINCTCOUNT(Visits[MembershipID]), 
               FILTER(
                    Visits,
                    Visits[MembershipID]=_id&&
                    Visits[Date]>=Memberships[Enddate minus 1YR]
               )
           )
)

     

     

    About optimizations for Power BI, I'd like to suggest you refer to the document .

     

    Best Regards

    Allan

     

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