Month: April 2019

Power BI: Resolving one case of “Access to the Resource is Forbidden”

Just last week while returning to work on a Power BI report I get this message when trying to refresh the data:

What the heck?  In Data Source Settings everything looked fine.  My report used a couple of OneDrive connections that looked OK, for example:

After a while of double-checking folder locations, file names, permissions, etc., I thought I would just try removing and reentering the credentials in the dialog above and voilà, problem solved.

Please follow and like us:

Power BI Best Practice: Use Parameters for Connection Information

This best practice is a simple corollary from a software engineering principle: never hard code values in code!  I am singling out connection information as in Power BI all work begins by connecting to one or more data sources.  You should always use parameters to refer to server addresses, database names, folder paths, and file names, for example.  For a tutorial on parameters see this article from Microsoft: http://tinyurl.com/hwftfda.

There are several benefits to this:

  • More readable code.  It’s easier to understand this M code:

    Source = Sql.Database ( #”QA Server”, #”CRM Database” )

    than this M code:

    Source = Sql.Database ( “333.444.55.66”, “DB459” )

  • Changes are easier to make.  If you have several queries against a database, for example, and the server address or the database name changes, then you only need to change parameter values, not each query’s code.  Consider a scenario in which you begin report development against data sources in a test environment.  When the time comes to test against the production environment it is more convenient to make the switch by editing parameter values.
  • Dependencies are explicit.  By just looking at parameter names and values you can tell what a report’s data source dependencies are.
  • Parameters can be changed in the Power BI service.  If connection information changes after publishing a report, parameters may be edited online in the dataset’s settings.  No need to republish the report.  Following a previous example, consider a situation in which you don’t have access to production data sources.  You may publish the report with the test environment’s parameter values and then edit the connection information after publication.  (For this to work the Power BI data gateway needs to have the production environment’s data sources defined.)

Please follow and like us:

Using Z-Order to “Hide” Visuals Targeted for Phone Layout in Power BI

One important but often overlooked design requirement for a report page
is whether it will be viewed in phones and not only in desktop devices. And even
when we are aware of such a requirement it might be a challenge to use visuals
that work well for both kinds of devices. This is because in Power BI the desktop
layout is the principal layout: the phone layout simply allows you to choose from the visuals already present and configured in the desktop layout. But sometimes we need visuals targeted for phone layout only.

Such a need may arise with “wide” visuals, such as a column chart with many categories in its X axis, a situation which I faced in a recent report that led me to write about this. The visual in question displays amounts by day number:

On a phone this visual will require heavy scrolling, which is quite inconvenient, as evidenced when viewed in phone layout:

A bar chart would be more suitable for a phone as there is “infinite” vertical space available.  In this image I chose to cap it at 24 days but could have gone the full 30:

So what to do?

Without layout independence one might think that an easy solution would be that a visual designed and configured specially for phone view might just be hidden (via the Selection Pane) in desktop layout and then selected in phone layout. However, while the visual may appear to be available when you switch to phone layout:

when you drag it onto the phone canvas it “disappears.”  This is what I see after dragging it to the top of the canvas:

My solution to this was to use the z-order setting for visuals to “hide” my phone visual behind the desktop visual (I also did this for a few other phone-only visuals):

The ideal situation would be to have more design independence on the phone layout rather than having to resort to such stratagems.

Please follow and like us: