The great thing about Power BI is it makes creating advanced models and visuals simple for people like me who have come from a background of being an Excel Jockey. Here are some common pitfalls I’ve seen people make when first using Power BI.
Thinking DAX functions are the same as Excel functions
DAX is deceptively similar to Excel; DAX has many of the functions you get in Excel and functions are built in a similar way. There enough differences that cause headaches when trying to do what you used to do in Excel.
Take your logic functions; AND and OR. Initially they seem the same but in reality the DAX versions can only contain two arguments. Building logic chains with these functions can get very complicated.
IF also behaves in a different way. In Excel you can leave off the false argument and it will return FALSE by default. If you leave this argument off your DAX function, you instead get a blank return. No need to put those double quote marks in the third argument anymore!
Continuing to shape data in Excel
Probably because DAX is frustratingly similar-but-different, I often see people continuing to transform data in Excel. One of Power BI’s selling points is that it is so much more efficient at transforming data than using regular Excel formulas. You can set refresh schedules as well so you literally have to do nothing once you’ve set it up.
Take the time to learn what you would normally do in Excel in a combination of M (used by the query editor) or DAX. This will pay off in no time at all.
Coding DAX on a single line
Unlike in Excel you can format a DAX formula in a similar way that you might for VBA. You can, and should, write your code on multiple lines and use indents. You can even add comments rows by preceding them with //.
Using good formatting makes it easier to go back and understand what code is doing. I’ve gone back to old code that I’ve done on a single line and it makes it so much harder to figure out what you were trying to do.
Try using the Dax Formatter tool by SQLBI to help you.
Using a calculated column instead of a measure
I’ve seen so many people struggle with a formula not returning what they wanted when all they needed to do was create a measure instead of a calculated column – sometimes the formula didn’t need any altering at all!
Measures are key to Power BI. Most Excel users will start off by creating calculated columns as it is a similar principle to how you would work in Excel.
Using Excel in the same way as before
The biggest mistake people make is thinking Power BI and Excel are two separate products. In reality Power BI is built upon plug-ins for Excel. The same query editor used in Power BI is now a core part of Excel 2016. In previous versions this is available as a free plugin called Power Query. The DAX language also originates from the Power Pivot plugin available since Excel 2010.
The same time-saving queries that you run in Power BI can be run in Excel as well. There’s no longer any need to try and create VBA code to merge data from multiple files together.
Design your spreadsheets with this query module in mind. Whenever I design an Excel based submission template I will now always put the data I want to extract in table format with a specific table name. I do this because I know this makes it much easier to identify the correct data within the query module. It makes designing these queries a breeze.