Press "Enter" to skip to content

Category: Power BI

Header Vs Title In Power BI Slicers

Published 2017-04-21 by Kevin Feasel

Callum Green notes the trade-offs between using a Header versus a Title in a Power BI slicer visual:

o   Header can have an Outline, which includes the ability to underline text.

o   Header is constrained to displaying the name of the attribute (“Product Category Name”), whereas a Title can be customised (“Select Category”).  You can rename your source data attribute to get around this, however.

o   Title enables you align the text, but this is not possible with a Header.

o   Header contains the “Clear Selection” option.

It’s not usually great to have both, but there are definitely trade-offs.

Comments closed

Dynamic Filtering With Power BI + SSAS

Published 2017-04-21 by Kevin Feasel

Patrick LeBlanc shows in a video how to implement dynamic filtering with SSAS Tabular & Multidimensional in Power BI:

In this video, Patrick answers your question about how to do this in Analysis Services Tabular and Multidimensional. Also, he adds a little bit of SQL to the mix.

Make sure to watch the previous dynamic filtering videos to understand the basics of how to do this.

To begin, you need to make sure to get the URL for your published report.

I completely agree with Patrick about doing as much as you can in the source, especially if there will be more than one potential consumer aside from Analysis Services.

Comments closed

Ignoring SSAS Dynamic Formatting

Published 2017-04-19 by Kevin Feasel

Chris Webb shows that tools like Power BI ignore formatting in SCOPE statements:

What’s more (and this is a bit strange) if you look at the DAX queries that are generated by Power BI to get data from the cube, they now request a new column to get the format string for the measure even though that format string isn’t used. Since it increases the amount of data returned by the query much larger, this extra column can have a negative impact on query performance if you’re bringing back large amounts of data.

There is no way of avoiding this problem at the moment, unfortunately. If you need to display formatted values in Power BI you will have to create a calculated measure that returns the value of your original measure, set the format string property on that calculated measure appropriately, and use that calculated measure in your Power BI reports instead:

Click through for more details and a workaround.

Comments closed

Case-Insensitive Power Query Sorts

Published 2017-04-18 by Kevin Feasel

Cedric Charlier points out a comaprisonCriteria on Table.Sort in Power Query:

Have you already tried to sort a table based on a text field? The result is usually a surprise for most people. M language has a specific implementation of the sort engine for text where upper case letters are always ordered before lower case letters. It means that Z is always before a. In the example (here under), Fishing Rod is sorted before Fishing net.

The classical trick to escape from this weird behavior is to create a new column containing the upper case version of the text that will be used to sort your table, then configure the sort operation on this newly created column. This is a two steps approach (Three steps, if you take into account the need to remove the new column). Nothing bad with this except that it obfuscates the code and I hate that.

Click through to learn a more elegant way of sorting.

Comments closed

Power BI Row-Level Security

Published 2017-04-17 by Kevin Feasel

Steve Hughes has some resources on implementing row-level security in Power BI:

Row level security is the ability to filter content based on a users role. There are two primary ways to implement row level security in Power BI – through Power BI or using SSAS. Power BI has the ability in the desktop to create roles based on DAX filters which affect what users see in the various assets in Power BI.

In order for this to work, you will need to deploy to a Workspace where users only have read permissions. If the members of the group associated to the Workspace have edit permissions, row level security in Power BI will be ignored.

Read on for more details as well as a set of how-to links.

Comments closed

Data Classification In Power BI

Published 2017-04-14 by Kevin Feasel

Steve Hughes describes how Power BI data classification works:

Power BI Privacy Levels “specify an isolation level that defines the degree that one data source will be isolated from other data sources”. After working through some testing scenarios and trying to discover the real impact to data security, I was unable to effectively show how this might have any bearing on data security in Power BI. During one test was I shown a warning about using data from a website with data I had marked Organizational and Private. In all cases, I was able to merge the data in the query and in the relationships with no warning or filtering. All of the documentation makes the same statement and most bloggers are restating what is found in the Power BI documentation as were not helpful. My takeaway after reviewing this for a significant amount of time is to not consider these settings when evaluating data security in Power BI. I welcome comments or additional references which actually demonstrate how this isolation actually works in practice. In most cases, we are using organizational data within our Power BI solutions and will not be impacted by this setting and my find improved performance when disabling it.

As Steve notes, this is not really a security feature.  Instead, it’s intended to be more a warning to users about which data is confidential and which is publicly-sharable .

Comments closed

On-Prem Power BI Gateway

Published 2017-04-13 by Kevin Feasel

Steve Hughes shows how to set up a data gateway for Power BI:

First, I will not be discussing the personal gateway in this post. If you have chosen to use the personal gateway, you have limited functionality and should consider using the on-premises data gateway for corporate use.

The on-premises data gateway (referred to as gateway throughout this post) “acts as a bridge, providing quick and secure data transfer between on-premises data and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.” (ref) Much of what is discussed here will apply to all of the services referenced above, but our primary concern is related to Power BI. Please refer to references at the end of this post for details about data sources supported within the gateway.

Click through for more information.

Comments closed

Power BI Quick Measures

Published 2017-04-12 by Kevin Feasel

Paul Turley has a post on the new “Quick measures” functionality in Power BI:

I had added the new Quick Measures feature to Power BI Desktop in the Options/Preview page.  This, apparently disables Quick Calcs and enables Quick Measures.  Although it flustered me me for a minute in front of an audience, I found this to be welcome news.  So, what is Quick Measures?  It’s a DAX calculation generator that automatically writes useful measures.  Here’s how it works…  Start by right-clicking or clicking on the ellipsis for a numeric column in the Field list and choose Quick measure…

The Quick measures dialog prompts for the necessary fields, which might be different for each calculation.  The tool generates appropriately formatted DAX calculations.  It even includes conditions to raise errors if used in the wrong context.  There are currently 19 different calculation variations that the tool will generate.  Following are two examples.  Creating a Quick measure from my [Flights] measure and choosing the Airline field for categorization produces this calculation:

Looks to be interesting.  Read the whole thing.

Comments closed

Sharing Power BI Data

Published 2017-04-12 by Kevin Feasel

Steve Hughes is starting a series on Power BI security:

Another way to compartmentalize or secure data is using Workspaces within Power BI. Every user, including free users, have access to My Workspace which is the default location for deploying Power BI and other BI assets. However, you also have the option to create additional workspaces as deployment targets. These Group Workspaces usually have functional and security separation associated with them.

This post is a good overview of methods available for data sharing.

Comments closed