Forum Discussion
Converting Lengthy, Complex Excel Formulas into DAX for Power BI Reporting Best Practices?
Hi all - I've been tasked with creating a data model in DAX/Power BI that replicates calculations from a complex Excel workbook which is full of conditionals, locked and relative cell references, column and row aggregations and other kinds of typical Excel formulas. Much of these are done to render a clean reporting view of the data, so I figured there's got to be a simpler, easier way to break down these calculations in DAX?
I've been able to make some progress on about half of it, but am stuck on the formula below.
I was curious to know if someone could offer some more generalized advice on how to approach these kinds of conversions or re-creations of Excel formulas. In Excel, I find many of the nested conditionals to be unreadable and confusing.
I'm somewhat adept at DAX, but certain concepts still present challenges for me, so I was hoping this would be a great learning opportunity to really improve my skills and knowledge.
Does anyone have any input or ideas about how best to approach solving for these kinds of conversion tasks from Excel into DAX?
Anonymous,
I would start by creating a set of base measures such as Revenue, Costs, etc. Then use these base measures as building blocks for more complex measures. It appears that rows correlate to measures and columns slice by region. Cell B19 is referenced three times in the formula, so you could create a measure that is specific to Region 1 (use CALCULATE with a base measure, filtered for Region 1) and then use that measure in the final measure (cell B20).
The 20% factor can be specified via a parameter (Numeric range, formerly What-if).
If you want the ability to create custom rows that are calculations of other rows, as well as apply custom formatting, try the matrix visuals below:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/xviz.inforiver-premium
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200000642
4 Replies
Anonymous,
Here are some tips that may help.
Format Excel formulas for readability:
https://www.excelformulabeautifier.com
Use the SWITCH function instead of nested IFs.
Use "&&" instead of AND, and "||" instead of OR.
Use variables for readability, reusability, and troubleshooting. When you create a variable, it can be used throughout the measure/calculated column. Variables are also helpful when troubleshooting. You can easily return a specific variable, allowing you to step through the code and find the error.
Format your DAX:
Thank you, the Excel JS formatter is fantastic; and I've been using the DAX Formatter for a while now.
What's the best practice to attempt to replicate this logic in DAX? Obviously, there are simple best practices like utilizing SWITCH and some more standardized operators, but to attempt to rebuild and redevelop this excel formula logic in DAX seems quite daunting, and I honestly have no idea where to even start?
Anonymous,
I would start by creating a set of base measures such as Revenue, Costs, etc. Then use these base measures as building blocks for more complex measures. It appears that rows correlate to measures and columns slice by region. Cell B19 is referenced three times in the formula, so you could create a measure that is specific to Region 1 (use CALCULATE with a base measure, filtered for Region 1) and then use that measure in the final measure (cell B20).
The 20% factor can be specified via a parameter (Numeric range, formerly What-if).
If you want the ability to create custom rows that are calculations of other rows, as well as apply custom formatting, try the matrix visuals below:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/xviz.inforiver-premium
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200000642