• Power BI Consulting: What Is in the Course?

    |

    ,

    This course is launching April 8th, 2025 for $10 for 24 hours. Then it will be $50 until April 13th.

    Below is a summary of the contents of the course.

    Module 1 – Choosing to consult

    This module is a reality check on why you want to consult and what things you should consider before making the jump. Module 1 videos are available for free on YouTube and on the course site.

    In addition to the videos, there are 3 bonus docs:

    • Readiness Checklist. This is a checklist of thought exercises to make sure you are ready to take the leap.
    • Burn Rate Calculator. This is a simple excel file to estimate your monthly income and see how many months you can work with your existing savings.
    • Recommended Reading List. A list of recommended and optional reading, podcasts, and videos for each module.

    Module 2 – Paperwork

    Module 2 focuses on the paperwork involved with getting started. In short, you will want:

    The module also includes some quick demos on tracking time with Toggl and creating an invoice.

    Module 3 – Sales and Marketing

    This module covers the fundamentals of sales and marketing with core concepts like the AIDA model and the sales funnel. It talks about how consulting is a high-trust work, and your sales and marketing strategy should reflect that.

    Module 4 – How to Scope

    The scoping section covers what goes into a scope of work, and how to estimate time and overall scope. It explains what deliverables are and how they can vary in concreteness.

    It also includes a private custom GPT that you can interact with to practice gathering requirements. If you are stuck, there is a document with a list of questions to ask the GPT. I also very quickly demo using Microsoft Word to write a scope of work.

    Module 5 – How to Price and Contract

    This module talks about three of the main pricing models: hourly, flat rate, and value pricing. It explains how to estimate your hourly rate based on your salary and desired role.

    For contracting, the module covers the gist of what should go into a service agreement and what to watch out for. As an exercise, I’ve included an intentionally malicious service agreement that you need to review for problems. This exercise also has a custom GPT for practicing contract negotiation. As part of the exercise, I have a marked-up version of the contract if you are stuck finding problematic clauses.

    Module 6 – Your First Project

    This final module helps to answer the question of how you know you are ready skill-wise. It talks about some of the mental health hurdles to expect when working for yourself. Finally, it covers some specific technical details of Power BI consulting and that first customer.

  • SUM and SUMX often have identical performance.

    |

    , , ,

    For years, I told people to avoid iterators. I compared them to cursors in SQL, which are really bad, or for loops in C# which are normally fine. I knew that DAX was column based and that it often broke down when doing row-based operations, but I couldn’t tell you why.

    Advice to avoid iterators is often based on a misunderstanding and a misapprehension of how the Vertipaq engine works. If you are blindly giving this advice out, like I was, you are promoting a fundamental misunderstanding of how DAX works. We think that they are running row-by-agonizing-row (RBAR). Toiling away and wasting CPU.

    The truth is that SUM and SUMX are the same. Specifically, SUM is syntactic sugar for SUMX. That means when you write SUM, the engine functionally rewrites it as a SUMX. There is no performance difference. There is no execution difference. There are identical execution plans. You can look for yourself.

    Looking at the data

    Here is the evaluation of SUM over 100 million rows of Contoso generated data, gathered with DAX Studio. With caching off, it takes 13 milliseconds and performs a single scan operation.

    Here is SUMX over the same data. 15 ms, same scan operation, same xm_SQL output on the right. Any DAX within 4ms should be considered to have functionally identical performance, according to SQLBI.

    Here are the physical and logical execution plans for SUM:

    Here are the logical and physical plans for SUMX. Identical.

    Why the confusion?

    So why is this a point of confusion? It is good to avoid row-based operations in general, but the engine often optimizes those away behind the scenes. So a blanket ban on SUM is silly and misguided.

    The fact of the matter is that if you stick to functions like SUM then you will fall into the pit of success. You will have better performance, on average, because the code you write will better align with how the formula engine and the storage engine work. CALCULATE + SUM is like having a safety on your code and when you have to step outside of that and use iterators like SUMX or FILTER you know that you have to be more cautious.

    Sticking to SUM will force you to engage in patterns that often lead to better performance. But SUM by itself makes no difference.

    But beyond that, it’s easy to write really, really bad code with iterators. If you put an IF statement inside of your SUMX then you will see CALLBACKDATAID, which is a sign the storage engine is having to make calls to the formula engine to handle logic it can’t handle by itself. Depending on how poorly you write your SUMX, it may do the vast majority of the work in the formula engine instead of using the storage engine and sending back data caches.

    If you want to learn more, I recommend checking out the super comprehensive book by SQLBI or my course on performance tuning.

Recent Posts

Categories