One of my favorite Excel functions to do business modeling are Cube functions. They act as a more efficient replacement for SUMIFS, GETPIVOTDATA functions that serve to sum aggregated results with various filters applied. Cube functions (CUBEVALUE, CUBEMEMBER, CUBESET) will only work if you have a PowerPivot model or live cube connection in your Excel…
Power BI: add KPI arrow triangles to your Variance Metrics
Anybody up for coloring Egyptian pyramids with Power BI? Just kidding 🙂 How about creating nice looking triangle arrows for your comparison measures colored dynamically based on the values. My goal is to assign up and down arrows or rectangles next to the year-over-year (yoy) growth percentages, colored green for going up and red for…
Excel CUBEVALUE vs SUMIFS & GETPIVOTDATA
Once CUBEVALUE formula came out about 10 years ago along with PowerPivot, I was delighted because it had filled the gap that was missing in SUMIFS and GETPIVOTDATA formulas for certain usage scenario.
Creating user-friendly connection names for long SQL strings (Fabric Endpoint)
Ever wondered how to remember 54 character long, cryptic connection string to a SQL Warehouse in Fabric? It could become challenging when using SSMS to choose the right connection when you have a drop down of few Fabric SQL Endpoint connections. Who knows by heart 54 random character strings? Well, there is no direct way…
Test: ChatGPT vs Microsoft Copilot on image creation
ChatGPT Image Creation I tried comparing the quality of ChatGPT and Microsoft Copilot when it comes to creating an image. I learned that they both use DALL-E tool, but the results are still different as you will see the article. I chose the topic of chihuahua dog, since I find it amusing. The prompt engineering…
Excel GETPIVOTDATA function
GETPIVOTDATA is one of my favorite functions in Excel when it comes to retrieving and aggregating data. Its similar to SUMIFS and CUBEVALUE, but it always references a pivot table. What is the GETPIVOTDATA function? The GETPIVOTDATA function is a built-in function in Excel that allows you to retrieve data from a pivot table based…
Excel XLOOKUP function
You want to ride the track with a super power?! Go get XLOOKUP, it will definitely serve you well. The XLOOKUP function in Excel is a versatile and powerful tool designed to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP. It offers more flexibility and functionality, making it a favorite among Excel users for data retrieval tasks….
How to Disable the Research Pane in Excel
What an annoyance! I personally, have never found any use in Excel’s Research Pane, other that it being really annoying and becoming such a distraction if it pops up unexpectedly, just like that dude Jar-Jar-Binks in Star Wars! If you find yourself wanting to disable it, follow these steps: Method 1 (the only one I…
Excel CUBEVALUE Function
The CUBEVALUE function in Excel is a powerful tool used to retrieve data from OLAP (Online Analytical Processing) cubes. This function is particularly useful for those who need to perform complex data analysis and create detailed reports. Let’s dive into what makes the CUBEVALUE function so userful and how you can apply it effectively. The function is…
Create multiple Schema in Lakehouse in Fabric
One thing that I was waiting for that was missing is the ability to create different schema in Lakehouse in Fabric. As of today, it is already in preview mode and can be accessed. The advantage of creating multiple schema in your Lakehouse is that it allows you to organize tables and also provide access…
Everything you need to know about home servers
Do you have an old computer laying around? Or do you need a server for your company? No matter what’s the case, setting up a home server is much easier than you think. In this blog, you will learn the technical specification for a home server and everything else you need to setup. Without further…
Power BI – split budget into ratios via DAX
In certain times, you would like to split a higher grain number into lower grain by using some percentage ratios. For example, if you have a budget for Product Brand all up, we want to split further the budget into Product Names based on last year’s sales percentage ratios. You can do this by: Procedure…