I’m going to keep today’s post short and sweet.  The #1 missing feature in Power BI is overwhelmingly clear to me, #2 isn’t close, and addressing this should be quite simple for my friends and former colleagues at Microsoft.

“Help Me Ob-Wan Kenobi, You’re My Only Hope”

Missing Feature in Power BI

For Those of You Who Think I’m Not a Fan of Power BI, This is MY Jeep
(But I would be a 10x Bigger Fan If We Had This One Simple Feature)

 

The #1 missing feature in Power BI is…

I don’t know how we’ve managed to go this long without the ability to Filter in the Data View:

We BADLY Need Filter in Data View of Power BI Desktop

We BADLY Need Filter in Data View of Power BI Desktop.  Click the Image to Vote for This Feature!

As simple as it sounds, this is the #1 thing I miss when I leave the Excel Power Pivot environment for Power BI Desktop.

Why do I miss it?  Debugging and Validating, primarily.  When I’m getting results from a measure that I don’t trust, it is super common for me to filter a table in Power Pivot so that I’m just looking at a specific set of rows.  And then I often filter by a second column, and then sometimes a third, in order to see if there are any unexpected surprises in the data.  Scrolling through thousands of rows looking with my eyes just doesn’t cut it.

This is positively crucial, at least for me, and creating a Table or Matrix visualization to perform the same task is such a high-friction alternative that I’m usually tempted to start over in Power Pivot.  Ugh.

In fact this is the overwhelming #1 reason why, if I’m starting a model from scratch, I try to do as much of the work as I can in Power Pivot before converting over to PBIX format.

Vote Vote Vote!  We Can Make This Happen 🙂

Click This Link and Vote for This Feature!!

I’ve held off on posting about this forever, thinking that it was too obvious, and that OF COURSE this feature was on its way.  But I’ve been thinking that for literally years now, and it’s time to take action.

Turns out that Microsoft actually DOES listen to our votes, via ideas.powerbi.com, and they’ve got a long-running “voting” mechanism for this purpose.

So for the love of all that is good in this world, please vote for this feature.  Vote generously.

And if you’ve never voted before and therefore need to create an account in order to vote, please do.  There are more things we need them to do for us, and they all fall under ease of use like this.  This will be a series, of sorts, even though the impact of #1 on the list is much larger than the others’.

Other Advantages of Having This Feature

1) Just Seeing the Distinct Values in a column.  The filter dropdown in Power Pivot is a quick way to see all of the distinct values in that column (up until the display limit anyway).

What ARE the Distinct Values? Please Tell Us, Oh Power BI!

OK, There’s Six.  But What Are They?

2) Seeing Whether There are Blanks in a Column. OK, this is in some sense a re-hash of just seeing the distinct values, but VERY often the reason for a relationship failing to create is because of unexpected Blanks in a key column.  And heck, merely even seeing if a column that should NEVER be blank does in fact contain blanks is a big deal.  (Power Pivot was particularly awesome at this – even if there were too many distinct values to display, if there were Blanks, a Blank was ALWAYS displayed as the final checkbox.  This was, and is, nothing short of life-saving.

3) Teaching!  When teaching folks for the first time about measures and relationships and modeling in general, the ability to “step through” measure evaluation, with the visual aid of filtering the source tables…  it’s so valuable that I simply cannot in good conscience teach a Foundations course in Power BI (instead, we start with Power Pivot and then switch over to Power BI after the fundamentals are established.)

Let’s get Microsoft’s attention.  Let’s put more “Power” in “Power BI.”

Click This Link and Vote for This Feature!!

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 35 Comments

  1. Thanks for helping rally support for this, Rob. Can’t tell you how often I have to copy a column into Excel and Remove Duplicates just to see the distinct values.

    1. Hi Mike, a quicker way might be to Remove Other Columns and Remove Duplicates to see the distinct values, then delete those two steps to get back to the original dataset

      1. Thanks PhilC, if I’m already in Query Editor I sometimes do that too, but filtering there for multiple columns takes much longer than filtering a table in PowerPivot or Excel (at least with the size tables I work with). In Data view, I find it easier to just copy the table to Excel and do validation there.

  2. My missing features aside from the obvious free PBI reader view in Office for dashboard consumption are:

    1) The equivalent of the excellent DAX formatter by SQLBI

    2) Some kind of ‘measures grid’ equivalent so you can easily sense check the result of a measure rather than having to create a visual to see the result

    I know you can do these things via the equally great DAX studio but the more scratching around using different applications the more arduous things become

    3) Ability to have master data model and connect to it as a data source exposing the measures that have been created within it. So true ringfencing of master data model from satellites allowing satellite owner to do anything in anger (e.g. creating their own measures) without damaging the integrity of master which you can rely on as an always up-to-date data source

    4) My long term bugbear: intellisense / wizardry for creating bespoke M functions/queries. It’s a real nuisance having to refer to a separate guide to merely translate functions which come naturally in Excel to M

    Constructive criticism though!

  3. I voted for this. I do exactly what you describe in Excel Power Pivot all the time. When i am not sure of my results i go to the raw table and filter down to figure out what is going on. And i usually find I’ve made a logic error for a scenario I didn’t anticipate. A must have in Power BI Desktop.

  4. I know PBI, and I will vote but, I always want a “measure tree” feature in PP. There is an add on to extract them all but, when I am trying to figure “it” out and take a couple of different streets, I get lost going over my mess cleaning up. I lose things about 3 measures from my base. It would good to see what the parent/child/grand./great grand relationships measure wise. What is worse is when I go back to a 2 year old model to do some new dev and I spend a couple fo hours just sorting it out. I have tried to be organized a couple of different ways but, eventually, I get focused and toss all of my structure out the window while making hay. One that exports would be even better.,

  5. Yes yes yes!! This would be ace. I was thinking about the absence of filter the other day, when I had to create a table to check through the values. Have voted and am hoping others do too.

    I have an idea also. 2 windows ability, like in power pivot you can have data model in 1 screen and excel pivots/ power view in the other. That would be cool wouldn’t it?? Vote!! https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20041123-new-window-for-different-views

  6. Also the measure grid is a great idea. I thought I couldn’t find it at first until discovering it wasn’t there at all!

    1. Snap John! I got so used to using it in powerpivot I was completely shocked to find there was no equivalent. It was an easy way to debug because I could have a base measure then subsequent measures varying the filters and checking that the master agreed to the sum of their parts etc. Debugging is very fiddly via visuals and switching between an application like DAX studio back and forth is not ideal

  7. To filter table all the time I keep my query editor open. It would be a great help to have filter in data view in modelling. Adding my vote to this idea.

  8. Absolutely would be a big plus and time saver. Sometimes I do leave Power BI to do some testing in Excel. Having this feature will definitely be more productive (and I voted also!)

  9. Thanks for putting the weight of your blog behind such a good suggestion. I’m excited to see what else you support, as I know I’ve struggled to persuade people to move from the Excel environment to Power BI, even when what they’re doing is clearly better done in Power BI.

  10. That’s why I use excel…. this would be a very convenient feature to have to make work faster and more efficient. This is a great suggestion!!!!

  11. Any feature that Microsoft provided with PowerQuery, PowerPivot and PowerView should be available in Power BI. Also, we should be able to share our work with others without requiring a license.

  12. When I was first introduced to Power BI almost a year ago it was one of the first things that I went looking for to help me understand, refine and search for problems in reports that we were setting up. You’ve got my vote.

  13. Agree this is a much needed feature, but far from #1 as far as I’m concerned.

    Power BI is still less powerful than Excel as I’m unable to leverage MDX to create dynamic sets, e.g. last n weeks from the current member etc. I can use this feature when connected via ‘Analyse in Excel’ but not in Power BI itself. Also, DAX still lacks all the very powerful (and useful) functions around hierarchies central to MDX, e.g. Parent(), Leaves() etc.

    Granted, this is a limitation of DAX rather than Power BI but the point is I can work around it in Excel whereas I can’t in Power BI.

    1. Congratulations on being one of the handful of humans in the world who knows MDX 🙂

      You have to have an outsized brain to grasp that stuff. I tried many times and could never get it to “take.”

      1. That’s very generous Rob, thank you 🙂

        To be honest though, I learned MDX out was out of necessity rather than choice. The upside is that DAX feels easier by comparison + using it alongside Power BI/Pivot in ‘cubeformula dashboards’ is awesome!

    2. You should be able to work through most of these by modeling to make up for the lack of MDX style DAX functions. Not the best, but still doable. Also for Last N Weeks I believe there was an update that just now allows you to do that through page filtering. Its Relative Date Filtering, maybe released just today?

      1. Absolutely, I can work around most limitations. What I do now is to filter the page to the Top N weeks by adding an ordinal measure to each week. Haven’t figured out how to make work within a filter context though, i.e. Top N *up and until* whatever the user has selected.

        I’ll have a look in to this new feature though, thank you! I suspect there may be an issue around my table not being a date table though as I’m using a 5-4-4 type calendar but it’s worth a shot.

        1. Yes, if you are using 5-4-4 it may not work. I played with it for a little while last night. Only complaint of mine is that when doing Calendar Year look backs, there is no way to also include the current year also.

  14. I couldn’t agree more with this! The main thing that still keeps me doing my models in Power Pivot in Excel is I love having my “sandbox” where I can test and play how my measures interact with the data filters.

  15. The #1 Missing thing in Power BI Desktop / Power Pivot is the equivalent of the F9 key (Evaluate) of Excel or the F8 key in VBA
    DAX studio is fine but we need something built in

    Also the Filter drop down is any way on the way, remember we could earlier not sort a column in the data view now we can.

    The Power BI Team is following a fantastic strategy
    1. Set a Low Benchmark
    2. Take user suggestions
    3. Incrementally increase the bar

    Here is an Example : In Power BI Desktop – Few months ago we could not change the Font Color of the Text in a Text Box.
    Then I think in May or June update we could.
    My question is how did a Text box come to a production release without the ability to change the font color.

    Look at the Matrix visual – getting there but still not the same as Excel’s Pivot Table.

    My request to the PBI team is set your benchmarks keeping Excel as the Base and improve upon that

Leave a Comment or Question