Tag: #PowerBI

Field Parameters are one of my favourite recent additions to Power BI. The ability to turn a single chart into potentially dozens changes the way we think about putting variations of visuals on the page. It was a real wow moment for a client recently when I showed how field parameters for 5 fields and 5 measures could produce a single report page that replaced 25 of their existing reports.

While they theoretically don’t allow you to do much that you couldn’t previously with a disconnected slicer and a lot of DAX they build it faster and without the need to get heavily in to coding DAX. Anything that lowers the difficulty bar for users trying to make the most out of Power BI is a good thing in my book.

Unfortunately being a new feature and still in preview they’re still a little rough around the edges. The big two issues for me are:

• There’s no interface to make subsequent edits.
• Once you pick one or more options in a field parameter there’s no way to unselect all of them or switch to a ‘none’ option.

The first can be bypassed for now by editing the DAX that appears when you select the parameter in the fields list. The second is officially not possible but I stumbled on a couple of options recently.

Option 1 – Create A None Dimension

Option 1 is a bit more work but also a little more ‘official’ as an approach. Simply add an extra dimension to the field parameter called ‘None’ based on a new single valued column.
In my test run I downloaded a copy of the Contoso sample dataset and added the following calculated column to the Calendar table (calendar seemed like a good choice as in most models it will be connected to all your fact tables).

None = "None"

After it’s in place just add it to your field parameter as you would any other field.

The up side to this approach is that it’s all above board, the down sides are that you’ve added a new column to your model and that None doesn’t actually mean nothing is selected and instead you have something called “nothing” this means with none selected your legends don’t vanish and instead you have a single item called none.

The DAX for the field parameter now looks like this:

Chart Legend = {
    ("None", NAMEOF('Calendar'[None]), 0),
    ("ChannelName", NAMEOF('Channel'[ChannelName]), 1),
    ("ContinentName", NAMEOF('Geography'[ContinentName]), 2),
    ("StoreType", NAMEOF('Stores'[StoreType]), 3),
    ("ProductCategory", NAMEOF('ProductCategory'[ProductCategory]), 4)
}

Option 2 – Getting Dirty With The Dax

I stumbled on this approach while dealing with the issue mentioned above that there’s no edit interface for the list of fields in your parameter. The only way round this if you want to add another field or change the ordering is to pull up your sleeves, select the parameter in the fields list and start changing the definition.

While I was doing this a few days ago I wondered what would happen if I changed the NAMEOF() function to be a fixed value. Lo and behold we have a none option that behaves just as you’d expect it to, pick it and we get an empty legend\column\wherever you’re using your parameter. You don’t even have to create a placeholder column.

The code from my Contoso sample now looks like this:

Chart Legend = {
    ("None", "None", 0),
    ("ChannelName", NAMEOF('Channel'[ChannelName]), 1),
    ("ContinentName", NAMEOF('Geography'[ContinentName]), 2),
    ("StoreType", NAMEOF('Stores'[StoreType]), 3),
    ("ProductCategory", NAMEOF('ProductCategory'[ProductCategory]), 4)
}

This option comes with the warning that it’s in no way a supported approach. We’re basically breaking the parameter by making it look for a field that doesn’t exist and there could be consequences I’m not aware of. If you fel this is too much of a risk then stick with option 1.