Frustrating Learning of the Week

I am Microsoft PFE for Data & AI. I am always learning something new; I wish I had time to post about all my learning and experiences. This role is rewarding and frustrating sometimes. More rewarding than frustrating, or I wouldn’t be in it for nine years running.
Anyhow, while working on a moderate size model (2.5GB) of Power BI dashboard with my DSE, we hit a significant bottleneck this week. Adding a new measure would take us about 60-70 seconds. It became very frustrating to point we were ready to throw the brick through his whiteboard.

None of the tools, DAX Studio, VertiPAQ, or Performance Analyzer, told me why?

We had to delete one element at a time until we found the root cause.

A calculated column,
DaysSinceCreated = DATEDIFF(ObjectDate,Today(),Day)

My understanding was “calculated columns are only reviewed during creation,” however, so WRONG I was. Talking to folks internally, I learned a valuable lesson today. Power BI pre-processes calculation of every computed column while making any model changes. Therefore, the more computed column you have the higher the cost. I know best practice is to minimize them in the model to minimize the model size and memory but never for this.

I would not have looked at this computed column as it was only 4MB in the 2.5GB model. I wish I knew that I wish there were a tool that helps identify that. We wasted half-day to track this down by deleting one component at a time.

Best practice, move them to the ETL job outside Power BI, or to Power Query (so maybe it can be query folded).

PS My goal was to get posts every month, but work-life-blogging life doesn’t work together for me. Maybe one day in the future it will.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.