Tag Archives: dax

  • Power BI 101 for an Excel User: Read this before you use Power BI


    For the better part of the last 2 years, I’ve been most of my time working “on the field” getting to know each and every user persona of Power Pivot, Power Query and Power BI in general.

    This is one of the reasons why I didn’t post that much during the 2016-2018 period. I did a full research on my own to better understand the user personas, what their pain points are and how to better reach these with techniques and patterns that are applicable to them.

    I’ve learned a lot from these people and one of the main situations that most new Power BI users that come from Excel face is the fact that they try to tackle their scenarios the same way that they would tackle them inside of Excel, which usually prevents them from taking the full advantage of what Power BI has to offer and at times it makes them waste way too much time in their initial steps because of preconceived ideas.

    In this post I’d like to talk about the main scenario that I see and it has to do with overusing DAX for any situation that you can think of, why this happens and start the conversation on how you can avoid placing yourself or any of your colleagues in this position so you can fully take advantage of what Power BI has to offer which goes beyond just DAX.

  • Relative Date Filters in Power BI / DAX / Power Query

    A few years ago (in march of 2013!), I wrote a guest blog post on Rob’s blog (PowerPivotPro) on how to do relative date filters. The title of that post was “how to always show Yesterday, Today, or Tomorrow’s Data”  with DAX in PowerPivot back in Excel. The post tried to address the issue that […]

  • The most important thing to learn when using Power BI


    You’ve started OR you’re in the middle of your Power BI journey and you’re confused as to where you should allocate your learning time and efforts

    Should it be DAX? Understanding the visualizations? the M language? Power Query? Power Pivot?

    SOOOO many keywords that appear when doing a simple online search, but WHAT is the core of everything inside of Power BI?

    This article covers my thoughts on where you should primarily focus your efforts when learning Power BI – let’s find out what is the HEART of Power BI.

    After spending more than 6 years using the toolset and going through various iterations and stages of what we now know as Power BI, these are my own thoughts and what I’ve found works best for the people that I’ve trained over the years.

  • Comparing Similar Products (Movies) with Power BI

    Back Story: Back in the day, I used to work for this company with a truly all-star / amazing team. We were all part of this company called 20Th Century Fox, and we were in charge of the Theatrical distribution of films in Central America.

    Business Intelligence wasn’t a standard in the industry – at least not how we know BI nowadays, and I was the main person leading the charge of using BI tools to analyze the sales aspect of the business – specifically the Box Office. And that’s how I found PowerPivot (without the spaces back then) and the DAX language. I went deep into it and even took the online Power Pivot Workshop that Marco & Alberto used to deliver back in December of 2011 .

    I was able to implement a WHOLE bunch of reports, analysis and dashboards thanks to Power Pivot, but one thing that I couldn’t implement back then (because I started the journey with my own company), was a Comparison of “Similar” movies for the release planning of an upcoming movie.

    See…before a film gets to a certain theater / cinema / studio / location, it has to go through a certain planning phase. It starts with an estimate on how much that movie might be able to make, but….how can we tell?

    Well, you had a few options, but in the end it was all a manual approach where you’d need to think about what movies might be similar to this one (from every single perspective that you can imagine). Then you’d go through the system and search for every single similar movie one by one so you can grab what was the total gross revenue for each.

    What if…you had a table for all the similar movies (usually called Comps) and you could simply select the Movie that you’re interested in and it’ll display all of the Comps?

    Of course, this doesn’t only apply to movies, but to any product / service that you’d like to compare against similar products.

    This is where this blog post comes in!

  • Powerpivot Success Case: The Motion Picture Association in Central America and the Caribbean


    Recently, I resigned my position as a Sales Analyst for the 20Th Century Fox in Central America to chase my interests as a Technology Evangelist for the Tabular Model and Powerpivot (specially Powerpivot) and now, with this spare time, I have time and the right granted access to tell you how I managed to save the MPA and the members of the MPA (Fox, Sony, Disney, Paramount, etc, etc) around $40K and still counting in terms of a BI Solution.

    Read more about this! It’s my masterpiece so far Smile

  • Understanding Tables, Relationships and Data Modeling in Powerpivot


    Figure 1: Shows a fact table with 2 dimension tables for Customers and SalesPerson

    My response to the image above 2 years ago as an excel user: “What the heck is a FACT and DIM table?Disappointed smile

    One of the common questions that anyone using Powerpivot has is… How should I model my Data in Powerpivot? and it’s not rocket science… it’s easy to learn and hopefully you’ll understand more about it after you finish reading this post.

    If you are new to Powerpivot and new to database modeling then the diagram above makes little to no sense but just think about them as lookup tables that you’ll later be doing some VLOOKUPS, MATCH/INDEX and all those formulas that we, as excel users, know about.

    Let’s learn more about what you should consider when building your Data Model!

  • Current and upcoming youtube videos

    This will be a short post since I’m trying to get back to record some youtube videos. If you haven’t seen any of my videos you can go here


    to see one of the most viewed videos in my channel but this post is primarily to get YOU, the reader, a suggestion on what I should be doing next or what you want to see on that channel.

    Hope you like the videos and leave some comments with your opinions or suggestions!

  • New project – Powerful free BI Solution for the Small Companies (Part 1)


    (From left to right: Visual Basic Studio, Visual Basic for Applications, Excel 2013, Powerpivot, Windows Task Scheduler) 


    Most cost-effective BI solution ever

    By now, you’ve probably heard about Cloud Powerpivot, SSAS Tabular and even MOLAP or plain MDX in SSRS all either deployed in Sharepoint or using the classic reporting server but… have you ever heard about the cheapest BI solution using Powerpivot?

    If your company doesn’t have the money to run a full BI Site in-house or get a hosting service for your Powerpivot Workbooks then you might want to read the rest of this post which will lead you to a free solution aimed towards the small to mid-size companies.

  • Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s


                                                 Executive: Are these values correct?

                                                Excel-guy: yes, but you need to check the dates

                                                                 slicers to see what dates the report is using

                                              Executive: Ugh… I just want to click on the report and

                                                                       see the latest values

    If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe  (forecasting or that sort of scenario).

  • What is Powerpivot? (The Million-dollar question)

    The Riddle

    (The Riddler)

    While the prize on whoever answers this question varies, it could potentially be worth more to any business in terms of ROI.

    In short, Powerpivot is the new tool that allows/enables the business users to take a much more important role in the creation of insights that are relevant to the decision making process. While traditional BI relies almost entirely on the IT department to create the reports, understand the business logic and translate the data into an insight that could be consumed by the end users, Powerpivot transforms this into a user-driven BI solution where the business users can actually define what they really want from the IT infrastructure and be able to break the barrier of understanding between the IT person and the end user… a new beginning

    If you are one of the thousands of excel users that struggles with creating new VBA codes or trying to come up with solutions that are semi-dynamic because of the limitations of traditional Excel, then Powerpivot will make your life easier in almost every way.

    Wan to learn How?