skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

HASONEVALUE vs ISFILTERED vs HASONEFILTER - Three Similar PowerPivot DAX Functions

The “Return Blank for Totals” Measure Written Three Different Ways – via HASONEVALUE, ISFILTERED, and HASONEFILTER.  So far, only HASONEVALUE seems to have a flaw.

Picking up from Tuesday’s post, which drew some quick comments, let’s add one more function to our evaluation:  HASONEFILTER().

Here are the three measure formulas from the pivot above.  They are all the same except for the highlighted function:

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

As covered on Tuesday, HASONEVALUE “fails” when you have a parent item (Region 0, in this case) that only has one child item (Store 26 in this case). 

But ISFILTERED and HASONEFILTER do not have that problem, and they seem to be returning the exact same results.  Let’s “differentiate” them shall we?

Bring in the Slicer!

Really, it’s not a “legitimate” post until a slicer shows up.  I should make this a rule or something:  never get involved in a land war in Asia, never get out of the boat, and always include a slicer.  (Who can name the two movies that are the sources of those first two rules?)


OK, now all three measures do different things, and only
HASONEFILTER continues to suppress subtotals.

Before the slicer, ISFILTERED was returning TRUE only in the non-subtotal cells.  Here, let’s go back:


Before the Slicer, Only These Rows Had a Filter Set on StoreName,
so ISFILTERED Returned TRUE Only for These Rows

But with the slicer, we get:


With the Slicer in Place and Selections Made,
ALL Cells in the Pivot Are Now Filtered on StoreName.


In short, HASONEFILTER is the most reliable way to detect total cells.

But…  there’s an exception.

One Final Point for HASONEVALUE

Let’s take StoreName off of the pivot and replace it with the StoreID field:


Swapping out StoreName for StoreID and Removing the Slicer Yields a Pivot Where Only HASONEVALUE “Outperforms” the Other Measures

Looking back, all three measure referenced the StoreName field directly:

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

Well, since StoreName is no longer on the pivot anywhere, there are no filters set on StoreName, so those two measures start going BLANK everywhere.

But HASONEVALUE is smarter.  StoreID=10 filters the Stores table to a single row, and that means just one value for StoreName as well:


When the Stores Table is Filtered to StoreID=10, There is Only One Value for StoreName:
This is Why HASONEVALUE Still Mostly Works

So, if you have lots of different fields that represent a store – StoreName, StoreID, StoreAddress, etc., and you plan to use different fields on different pivots, HASONEVALUE still might be the way to go.

So we still have choices to make from time to time Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 14 Comments
      1. It helps we watched Princess Bride last month. It’s a household thing here. And our kids had never seen Willow, so we indoctrinated them to that as well. 🙂

  1. Rob, you should have included ISCROSSFILTERED for completeness.
    On page 234 of your book you state it “is still quite foreign to me.” This post would’ve been the perfect place to experiment with it.

  2. Working a (what seems imposable) problem, and one of this arguments may work, but its becoming a brain teaser for me, so if anyone sees this and has a solution, I will offer my: “Inconceivable” !! (also Princess Bride)



    Rows have Organizational Hierarchy (area, district, cluster, store)

    Values have Calculated Fields

    So looking to create a formula that would look like this (if it was a simple formula)

    If(AND(District[Calculated Field A]>0, Cluster[Calculated Field B]>0), “True”, “False”)

    Can this be done with HASONEVALUE or any other argument ?

    Very much appreciate any help !


    1. So you want to evaluate CalcFieldA purely in the context of District (like, at the District subtotal level, across all Clusters), and then similarly evaluate CalcFieldB at the Cluster level, across all Stores in that Cluster? If so, try this:

      CALCULATE([CalcFieldA], ALL(your cluster table and column names), ALL(store table and column name)) > 0,
      CALCULATE([CalcFieldA], ALL(store table and column name)) > 0

      1. So I have two points:

        1) I _believe_ the second “CALCULATE” function should actually be referring to “CalcFieldB”, correct?

        2) Assuming that that’s true, then it made a lot more sense why the formula for CalcFieldA doesn’t actually refer to Districts, and the formula for CalcFieldB doesn’t refer to clusters.

        In both cases, you’re using “ALL()” to aggregate the data _within_ your target scope. Since you always want to report CalcFieldA at the “District” level, that means you need to lump together “ALL” the Clusters and “ALL” the Stores within each. (Similarly, if you wanted to aggregate “CalcFieldC” within each “Area”, you’d calculate it across “All Districts, Clusters and Stores”.)

        Does that sound right?

  3. When I use HASONEVALUE to filter out the sub totals I find that slicers do not work well. If the slicer is the same field as the sub total then the slicer thinks that field has not value. You can sill select the greyed out slicer options but they are greyed out or not there at all if you select “Hide items with no value” in slicer settings. Any way around this?

  4. I am trying to use this in the context of a calculated column rather than a measure in order to use a user selection as a variable
    LimitSelection= IF(HASONEVALUE(WOSLimit[Limit]),WOSLimit[Limit])

    This hasn’t worked for me in a calculated column. I have also tried creating a measure for the LimitSelection to replace the VAR in the above formula and referencing that measure in the return formula; however, that is not working either. It appears as though the context of the calculated column does not recognize that a selection has been made.
    Does anyone know of a fix for this?

  5. hey, still a newb with powerQ PowerPivots datamodels dax cubes and so on 🙂

    Cubes are stille very elusive for me, but i wanna get to know them better and work smarter.

    I got a thing where i would like to have an input cell where co-workers can type in a customer name and have another cell show the customer ID. Sometimes it’s easier for people to remember a name than a number and i got a pivot that filters on customer ID. So having a place where they can type the name to get a number to use as filter would be a nice to have.

    I was wondering if i could use cubevalues for this lookup.. and so far my search has brought me to hasonevalue, but i don’t quite grasp how it works and how i would have to use it to essentially get a vlookup into the cube data.

    1. I figured it out with a little google help 😀

      =CUBEVALUE(“ThisWorkbookDataModel”;”[Measures].[Get Info]”;”[TABLE].[COLUMNNAME].&[“&I21&”]”)

      The table.columnname and &”[“& cellreference &”]” includes the text in the referenced cell as a text argument to filtering the measure.

      the getinfo measure was inspired and stolen from Tomallen in the thread Mine is abit more simple as i’m looking for just one thing.


      So.. i figured out that my problem was i couldn’t just simply reference a cell in the cubevalue formula, i had to include it as a dax argument if you will.

      My initial attempt that resulted in failure:

      =CUBEVALUE(“ThisWorkbookDataModel”;”[Measures].[Get info]”; I21)

      Hope others finds the information useful 🙂

  6. What about multiple selections? I have a case where if a combination of selected slicer elements are selected and a unique value can be determinted from them – then a visual must output data.

Leave a Comment or Question