Callum

Callum Green's Blog

Embed PowerApps into Power BI Desktop

Microsoft’s January 2018 Power BI Desktop update (blog found here) contains quite a few small visualisation features, but the one that stood out most to me is the ability to import PowerApps as a Custom Visual. My last two blogs (Part 1 and Part 2) demonstrated how to embed a PowerApp into a Power BI Dashboard in the service, but it wasn’t possible to achieve this in Power BI Desktop or a Report.  How things have changed within a month!

This article illustrates how quick and easy it now is to plug an existing PowerApp into Power BI Desktop.     

PowerApps Custom Visual

Like all popular Custom Visuals, you can either Import from file (stored on a local machine) or Import from store. The Microsoft endorsed Custom Visuals are found in the store, which is why I would generally advise importing visuals from there.  Search for “PowerApps” and it will appear as the top result.

clip_image002

Click ‘Add’ and the PowerApps Custom Visual is available.

clip_image004

NOTE:   The PowerApps Custom Visual is currently in Preview, meaning its reliability cannot be 100% guaranteed.  Always use Preview tools/features/visuals with caution.

PowerApp Connection

I previously created an ‘SvT Adfjusted’ PowerApp, which will be used for this example.   Firstly, the visual needs to have ‘data’.  It can be an attribute or measure, but doesn’t matter when connecting to an existing PowerApp.  If you haven’t logged into the PowerApps portal, you may be promoted to do so with Power BI Desktop.  Once logged in, you will see the below screen:

clip_image006

 If you see the ‘not supported’ error message above, do not worry – this is red herring.  Click ‘OK’ and then navigate to the appropriate Environment within the nested PowerApps portal.  ‘Adjust Target App’ resides in the ‘Callums Blog’ Environment.

clip_image008

Click ‘Choose App’, select the PowerApp and click ‘Add’.

clip_image010

That’s it, the PowerApp is now embedded and ready to use in Power BI Desktop.

clip_image012
It is also possible to create a new PowerApp within Power BI Desktop, which is demonstrated in this video. The look, feel and general experience is the same as what you see in the PowerApps portal, meaning you do not even need to use a web browser to get started.

PowerApps Refresh

I wander how long Power BI Desktop takes to refresh when a change is made to the embedded PowerApp?  Let’s find out.

Before:

clip_image014

Principal A ‘Target’ value is changed from 850000 to 950000.

clip_image016 

Unfortunately, nothing happens.  Power BI Desktop caches the data sourced from the Azure SQL Database, which is where the PowerApp data is stored.  The only ways to view the change is to click the Power BI ‘Refresh’ button or change the context of the visual interaction within a report.  What I mean by the latter is that you need to force the Tabular engine to write a new DAX query, which in turn, forces a query refresh.  Using the ‘Refresh’ button (below) will always be the simplest refresh method.  

clip_image017

After:

clip_image019

Published Report

The report can now be published to the Power BI Service.

clip_image021

The Service contains the same refresh issues as described in Power BI Desktop. Manually refreshing the web page (CTRL-F5) is currently the best approach.  It is worth noting there is between 15-30 seconds delay between a submitting a PowerApp change and viewing the new number in a visual.  This is expected, as there are a few processes (behind the scenes) that must happen first.

Web Content vs. Custom Visual

There are now two ways of adding a PowerApp into the Power BI Service – but which is better?

Web Content

-          Advantages

o   Easy to dynamically add a ‘Web Content’ tile to a Dashboard.

o   More flexibility around the link behavior e.g. open custom link or other report in a new tab.

o   The PowerApp sits completely outside of Power BI and does not necessarily need to be seen in a report.

-          Disadvantages

o   PowerApp embed code is required and may not be available to all users.

o   Extra layer of management and maintenance.

Custom Visual

-          Advantages

o   No increase in the Power BI Desktop data model or file size.  The PowerApp is sourced from the cloud and acts as a Live Connection.

o   One stop shop where both visuals and the PowerApp reside.

o   Less places for an issue to occur.  Security can also be resolved, using Power BI Desktop as a test.

o   PowerApp can be pinned as a visual - to an existing or new Dashboard.  This step is far more user friendly and easier to achieve with the Custom Visual.

-          Disadvantages

o   No ability to use custom links.

Whilst Web Content does offer a bit more flexibility and interactivity, I would advise using the Custom Visual wherever possible.  Having one place where everything is managed, makes everyone’s lives far easier.  If you have a good business case for adopting Web Content, then of course, please do so. 

References

Find other recommended resources below.

o   PowerApps Custom Visual Video - http://bit.ly/2Fi3vLY

o   Power BI January Update - http://bit.ly/2CSfLVl

o   PowerApps - http://bit.ly/2Brjys4

o   Flow - http://bit.ly/2CoL2vW

o   Common Data Service - http://bit.ly/2CnXXhv

Contact Me

If you have any questions or want to share your experiences with the PowerApps Custom Visual, feel free to leave a comment below.

Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences.

In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed.

I have broken the blog into 2 parts:

-          Part 1: How Power BI visuals and PowerApps can be used together.

-          Part 2: Benefits and Drawbacks of the tools/processes used.

Solution

I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard.

Create Sample Tables and Data in Azure SQL Database

Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.

clip_image001[4]           

clip_image002[4]

Note:     Azure SQL tables require a Primary Key column to communicate with Flow and consume CDS data.

Create PowerApp

Create an Environment within the PowerApps service, adding two new Connections:

 

clip_image003[4]

1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside.

2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.

 

The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.

 

clip_image005[4]

 

This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.

 

clip_image006[4]

 

Publish the App and test and change the ‘Target’ values to test.

Create Flow trigger

Navigate to https://emea.flow.microsoft.com/en-us/ and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.

 

clip_image008[4]

 

Select the PowerApp CDS Entity (Adjusted Target) as source.

 

clip_image009[4]

 

Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.

 

clip_image011[4] 

 

Create Power BI Report

Create a Power BI Desktop report and connect to the Azure SQL Database.

clip_image013[4]

Set up the one to one relationship on ‘PrincipalID’, between the tables.

clip_image014[4]

Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App.

clip_image016[4]

Embed Power App into Dashboard

Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].

 <iframe width="98%" height="98%" src="https://web.powerapps.com/webplayer/iframeapp?hideNavBar=true&source=powerbi&screenColor=rgba(165,34,55,1)&appId=
/providers/Microsoft.PowerApps/apps/
AppID
]

clip_image018[4]

 

The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.

clip_image020[4]clip_image022[4] 

 

 

Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!

 

clip_image024[4]clip_image026[4]

 

Further Reading

Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below.

o   Matt How’s Blog - http://bit.ly/2CpbTYI

o   Embed PowerApps into Power BI - http://bit.ly/2ywgsNX

o   PowerApps - http://bit.ly/2Brjys4

o   Flow - http://bit.ly/2CoL2vW

o   Common Data Service - http://bit.ly/2CnXXhv

Contact Me

If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.

Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 2

Part 2 of this blog focuses on my experiences with PowerApps, Flow and Power BI. Part 1 was more of a demo and ‘How to’ guide, but when I read an article online, I always find known limitations, challenges or workarounds as the most interesting takeaways. Without further ado, here are my findings.

 

A summary of both blogs below:

-          Part 1: How Power BI visuals and PowerApps can be used together.

-          Part 2: Benefits and Drawbacks of the tools/processes used.

Benefits

-          Easy to get started. Rolling out Power Apps, Flow and Azure databases into production of course needs careful thought, but for Proof of Concept’s, Flow (2,000 runs per month) and PowerApps (for Office 365 users) are free to use. Links to the price breakdowns are provided in the Further Reading section below.

-          There are a range of Wizards, Templates and GUI’s. All the tools used offer great templates for moving or inputting data and the fact barely any code is needed, makes it simple for business users. Following a couple of YouTube tutorials on each technology will get people up to speed very quickly.

-          Azure technologies provide seamless integration between Microsoft tools. Whilst there are some other well-known, reputable cloud service providers around, using one product is always going to produce a slicker solution. Having less configuration steps means less chance of human error.

-          Customisable features of PowerApps give the ability to mask, validate and format the PowerApp screens. It also makes the user entry a more pleasant experience, as the forms are more intuitive.

Limitations

-          You can only embed PowerApps into a Dashboard – as a Tile. I am not sure if moving PowerApps into a Power BI Report is on the roadmap, but I would be surprised if it was never supported.

-          Power BI Dashboards are cached and not entirely real time. You can change the cache settings to 15 minutes, but the best way to ensure your visuals contain the latest Power App data is to manually refresh your page in the browser. Reports do update automatically, which makes it even more frustrating.

-          Common Data Service (CDS) is a preview Data Connector in Power BI. As a result, you need to either have your environment set as ‘America’ and/or been given the beta by Microsoft. If I had access to this connector, there would have been no need to have the Azure SQL Database or Flow trigger. Milinda Vitharana’s blog shows how to enable CDS Power BI Integration.

-          If you wanted to use an on-premise database instead of an Azure database, an additional step is needed. A Data Gateway (link here) must be installed to move the Power App data back into the SQL database. Therefore, I would always recommend (where possible) using PaaS or other cloud services, as they talk to each other natively.

-          The error handling within the PowerApps is still quite limited. If Flow fails when updating data between PowerApps and Azure SQL Database, nothing is captured within the form itself. An Admin would need to check the Flow job or set up email alerts for user’s peace of mind.

 

clip_image002[4]

 

Conclusion

The initial signs look promising for Power BI and PowerApps integration. I managed to create an Actual vs Target Proof of Concept in just a matter of hours, without any real coding. There are still quite a few drawbacks and hoops to jump through to bring everything into a Power BI Dashboard, but I can only see things getting easier from this point.

There are other use cases for embedding a PowerApp into Power BI, such as monitoring live sales and re-ordering stock within a PowerApp or updating product descriptions that automatically updates the Dashboard attributes. Giving someone the ability to directly interact with a Dashboard and make instant business decisions is priceless in today’s fast paced world.

Further Reading

Find other recommended resources below.

o   PowerApps Pricing - http://bit.ly/2j5sN69

o   Flow Pricing - http://bit.ly/2kw0MFr

o   Milinda Vitharana’s blog - http://bit.ly/2BfkywQ

Contact Me

If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.

Twitter:            @DataVizWhizz

Themes in Power BI – Part 2

In September’s Power BI Desktop release, one of Microsoft’s announcements was a new offering of Themes.  My previous blog back in March 2017 explained what a Theme could (or couldn’t!) do, but I am pleased to say Microsoft listened to the community and have improved it greatly.

Originally, we were limited to setting a few page or chart colours - mainly tables and matrices.  Appreciated this was a first release from Microsoft, but it’s safe to say I was left uninspired.  The good news is Themes can now do a lot more, including:

-          Set the font size for specific charts e.g. 14

-          Set visuals to use a specific font type style e.g. Calibri

-          Turn off/on chart data labels

-          Set default property values for a chart

-          “*” functionality that will apply a specific property across all charts

This blog will focus on how to not only use the new features, but how they can be brought together to form a larger, organizational configuration file.

If you would like to follow my examples, please ensure you have downloaded the latest version of Power BI Desktop – found here.

New JSON

In order to extend the Adatis Theme, a new section called visualStyles must be added to the JSON.  The format is as follows:

clip_image001

The highlighted JSON sections give you the ability to use ‘*’ syntax, which means the Theme format will apply to everything underneath it.  This is useful for things if you want to apply a global font size across the entire report, rather than needing to configure every chart/visual.

Now, let’s pick a simple use case.  Client A requires the following to always be applied to a Pie Chart in Power BI Desktop:

-          Switch on the Pie Chart Legend

-          Set Text Size to 14

-          Set Font Family to Calibri Light.

Default Pie Chart:

clip_image003

-          By design, Legends are turned off.

The configuration file is tweaked to contain the new JSON - highlighted in yellow.

{

  "name": "Adatis",

  "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4", "#008DED", "#74B238", "#8EC954", "#DB9800", "#FFB60F" ],

  "background": "#FFFFFF",

  "foreground": "#002C5C",

  "tableAccent": "#81C341",

  "visualStyles": {

    "pieChart": {

      "*": {

        "legend": [

          {

            "show": true,

            "fontSize": 14,

            "fontFamily": "Calibri Light"

          }

        ]

      }

    }

  }

}

The final step is to import the modified JSON file, resulting in a completely different looking Pie Chart. 

Applied Theme:

clip_image005

1.       The traditional Adatis chart colours have been applied.

2.       A Legend is visible – in both the properties pane and the visual itself.

3.       Font family and Text Size are updated.

Adatis Theme Extended

I will now add some additional configurations to the Adatis Themes file.

-          Turn On all Chart Legends.

-          Pie Chart Label Style set to Percent of Total.

-          Set Card Label and Category Label Font Size to 12, but all other visuals use 10.

This changes the JSON code significantly, but by following the visualStyles format, this was more straightforward than it seems.

{

  "name": "Adatis",

  "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4", "#008DED", "#74B238", "#8EC954", "#DB9800", "#FFB60F" ],

  "background": "#FFFFFF",

  "foreground": "#002C5C",

  "tableAccent": "#81C341",

  "visualStyles": {

    "*": {

      "*": {

        "*": [

          {

            "fontSize": 10,

            "fontFamily":  "Calibri"

          }

        ],

         "legend":[{

                    "show":true

                }]

      }

    },

    "card": {

      "*": {

        "labels": [

          {

            "fontSize": 12

          }

        ],

        "categoryLabels": [

          {

            "fontSize": 12

          }

        ]

      }

    },

        "pieChart": {

          "*": {

            "labels": [

              {

                "labelStyle": "Percent of total"

              }

            ]

          }

     }

  }

}

The report is significantly different.  I will pinpoint a specific area of the report to illustrate where the configurations occurred.

Before:

clip_image007

After:

clip_image009

1.       Global font size is set to 10.

2.       Percent of Total setting applied to Pie Charts.

3.       Category Labels and Labels set to font size 12 for all Cards.

What else can Themes do?

This article is only scratching the surface at what Power BI Themes can now do.  Click here to find the extensive list of properties that can be configured.  It is worth mentioning that if you have limited JSON experience, some of the configurables can be a little tricky to set up.  However, there are quite a few blogs out there that can get anyone up to speed, which I will further provide below. 

Limitations

There are a couple of limitations, as well some things I have experienced:

-          Debugging JSON (natively within Power BI) is not supported and a syntax error occurs. Power BI will give you a generic error message like below:

clip_image011

-          Data configurations are not supported

·         This is understandable, though.  Every report tab contains different slices or filtering of data, but colours, fonts and chart properties tend to remain the same.

-          Setting Card font and background colours does not appear to be possible yet.  Or at least I have been unable to get it to work or find any supporting documentation from Microsoft to suggest it is.

-          Overriding a Theme

·         Individually changing a chart property will override the Theme JSON file.  I was caught out by this and couldn’t understand why the configuration wouldn’t change on a Pie Chart.

·         To get around this, use Revert to default within a chart property and it will re-apply the Theme.  This option exists in each top-level chart property – see example below.

clip_image013

 

Further Reading

o   Jason Lengstorf’s JSON Blog –  http://bit.ly/2aU1OHS

o   Power BI Desktop September Update - http://bit.ly/2y1QLWe

o   Power BI Report Themes BI Blog - http://bit.ly/2mPq69l

o   Themes Gallery (containing example JSON files) - http://bit.ly/2pyUKpl

Contact Me

If you would like a copy of the PBIX workbook/JSON Theme file or have any general questions, feel free to leave a comment below.  I am particularly interested to see what Theme files other people have created and if there any other known limitations.   

Twitter:                @DataVizWhizz

Direct Query vs Live Connection in Power BI – Part 2

This instalment of the Power BI blog series focuses on the two other differences between Direct Query and Live Connection in Power BI. 

If you haven’t done so already, be sure to check out Part 1, which concentrated on Quick Measures and Relationships.

Feature Differences

Find below a Power BI Direct Query screen, focusing on the final 2 differences (highlighted green):

o   New Hierarchy

o   Change to Import Mode

clip_image002

New Hierarchy

Hierarchies are very useful with Power BI, especially when wanting to drill up and down within levels of data e.g. Day > Month > Year. Let’s try and create a New Hierarchy in Live Connection mode:

clip_image004

New Hierarchy does not appear in the list of options.

This is because hierarchies are typically created within the OLAP Cube, therefore, it makes sense why the option is not available.  However, I would argue that Direct Query allows you create a hierarchy, so why can’t Live Connection?

Direct Query stores the hierarchies within a smaller Power BI model (Tabular Cube) running on a report developers local machine.  Ironically, Live Connection does exactly the same thing when you create an ad hoc measure in Power BI. It is surely a matter of time before hierarchies are also supported.

Change to Import Mode

Direct Query mode supports the ability to easily switch to Import Mode.  This is a useful option, especially for a self-serve analyst wanting to make transformations and shape the data.  By right clicking the highlighted option below, a simple wizard appears:

clip_image006

clip_image007

When the import completes, the data (from tables in the database) will be stored in a Tabular cube on the local machine where Power BI Desktop is running.  If we navigate to the same area within Live Connection, notice there is no option to change to Import Mode (“click to change”:

clip_image009

Once again, I am not really sure why this feature isn’t support in Live Connection mode.  I can understand why it would be more difficult to convert a Multidimensional Cube (MDX) into a local Tabular Cube (DAX), but if Live Connection already points to Tabular, it’s an exact copy. 

To vote for Import Mode functionality within Power BI Live connection, click here.

Coming Soon

Part 3 is the final instalment of the blog series, specifically focusing on the underlying Power BI Data Models in Direct Query and Live Connection.

Further Reading

Other than the Power BI Blog, there are some other great pages out there too:

o   Power BI Blog - http://bit.ly/2rkLGoq

o   Import Mode vs. Direct Query - http://bit.ly/2t4ragx

o   Direct Query in Power BI - http://bit.ly/2nUoLOG

o   Live Connection in Power BI – http://bit.ly/2tfJr5L  

Contact Me

If you have any questions or thoughts, please leave a comment below.  My Twitter details are also provided.

Twitter:                @DataVizWhizz

Direct Query vs. Live Connection in Power BI – Part 1

There are lots articles out there that compare Import Mode vs.Direct Query, but people rarely talk about if there are any differences between Direct Query and Live Connection.  “Wait. Aren’t they the same thing?”  Well, not quite.

The first big difference between Direct Query and Live Connection is the type of data source used for the connection.  The former uses a database connection (typically SQL Server), whilst the latter requires an Analysis Services OLAP Cube.

This blog series won’t explain what Direct Query and Live Connection can do (found here and here), but will instead highlight the other subtle differences between the two connections. 

Feature Differences

There aren’t any features I can find that are available in Live Connection and not Direct Query.  However, there are a few the other way around.

I will first show you a Power BI Direct Query screen, focusing on the 2 of the 4 differences (highlighted green):

o   Quick Measures

o   Relationships

clip_image002

Quick Measures

This feature was first released in April 2017 and is available in Import Mode and Direct Query.  It enables a non-technical Business Analyst to create relatively complex DAX aggregations, with the use of a nice Wizard.  To access Quick Measures, right click on a measure or attribute and select Quick Measures.  Let’s try the same thing in Live Connection mode.

clip_image003

You’ll notice that Quick Measures is missing from the list of options.

I find it bizarre that Live Connection doesn’t support Quick Measures, especially when using a Tabular Cube as the connection.  The Power BI DAX language and engine are the same as Tabular, so you would think the two are compatible!

Please vote for this feature to be added into Live Connection - http://bit.ly/2umsiJy.

Relationships

There are two tabs displayed on the left-hand pane in Direct Query mode.

clip_image004

If you click the highlighted tab, it opens a Relationships page – where you can begin to join datasets (from the database) together.  I created a manual relationship that joined DimEmployee and DimDate together – as shown below.  No relationships are created in the underlying SQL Server database, but instead stored within the Power BI model.

clip_image006

In Live connection, the left-hand pane looks bare:

clip_image008

There is no option to create any form of Relationship against the Live Connection Tabular Cube.  This kind of makes sense because a BI Developer would be the person responsible for creating relationships within an OLAP Cube.  I would argue that if you want the ability to mashup data or create your own relationships, you shouldn’t be connecting to a Cube in the first place.

Coming Soon

Check out Part 2 of my blog series - available here.  The focus of this article are the Add Hierarchy and Change to Import Mode features.

Part 3 will conclude the Trilogy, where I go off-piste slightly and focus on the Data Models in Direct Query and Live Connection.

Further Reading

Other than the Power BI Blog, there are some other great pages out there too:

o  Power BI Blog - http://bit.ly/2rkLGoq

o   Import Mode vs. Direct Query - http://bit.ly/2t4ragx

o   Direct Query in Power BI - http://bit.ly/2nUoLOG

o   Live Connection in Power BI – http://bit.ly/2tfJr5L  

Contact Me

If you would have any questions or thoughts, please leave a comment below.  My Twitter details are also provided.

Twitter:                @DataVizWhizz

Slicer Properties in Power BI: Header or Title?

I recently shared a Power BI Report with a customer and they reported that the “Clear Selections” option (Eraser icon) was not available when they used the Slicer.  It took me a while to work out why this was.

This blog will illustrate how you can lose the “Clear Selections” functionality, depending on what Format settings are applied to a Slicer.  I will also show how to work around the formatting constraints, which will help prevent you from ever experiencing the issue.

In order to follow the examples, you will need access to Adventure Works 2014 SSAS Tabular and of course, Power BI Desktop.  Follow the appropriate links to download what you need.

Use Case

I have created a very simple report (available on request), using a Product Category Slicer and a Map to display Internet Total Units. 

clip_image001[4]

o   Format property.

o   Select All is off, ensuring multi select is allowed.

o   Header is off.

o   Title is on, used instead of Header and configured to look like the below:

clip_image003[4]

After multi-selecting the attributes in the Slicer, I tried to “Clear Selections” – which is normally available as an option like below:

clip_image005[4]

However, it does not appear in the report I created:

clip_image007[4]

The reason I cannot see the “Clear Selections” option is because I am not using a Header.  When I use this instead of Title, the Slicer contains the required feature.

Header vs Title

This made me wonder what other differences are there between Title and Header but in fact, there aren’t many.  The subtle differences to be aware are displayed and described below:

clip_image001[12]

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.

Workarounds

There are couple of ways to work around the missing “Clear Selections” issue, which I will demonstrate below.


Option 1

I could simply switch from a Title to a Header, but then we would lose the ability to centre align the description.  Instead, we can set both options to ‘On’.

clip_image010[4]

 

After some formatting, the Slicer is pictured below.

clip_image012[4]

o   The Header text is set to White, therefore, it not visible. 

o   You can still see the “Clear Selections” option.

o   However, there is white space in between the Title and Header.  This not only wasted space, but also looks a bit strange from a visual perspective.

o   The actual sections themselves (e.g Bike) are a bit squashed and disproportionate to the Slicer border.

 

Option 2

The other workaround involves a little more work, but gives the impression that only a Title is being used, but with the added functionality of the “Clear Selections” option.  Furthermore, there is no longer the white, empty space.

 

clip_image014[4]

 

o   Two objects were used to produce the result:

·         Text Box

·         Slicer

o   The text box has been formatted and labelled with ‘Select Category’, using the Title property.  This allows you to change the font colour.

o   The Slicer is using the Header option, ensuring “Clear Selections” is available. Title is turned off to reduce the empty space.

 

Summary

So why are there two types of properties for Slicers and other objects like Text Boxes?  I am not entirely sure myself, but occasionally, you may want a Slicer to contain a Title with an Attribute Name as the Header underneath. This gives you a Title > Sub-Title concept (illustrated below).  I do understand why the “Clear Selections” feature is specific to the Header setting, as it directly relates to the Slicer attribute.  

There are certainly ways of solving this issue - here are just a couple of suggestions:

1.       Providing continuity across both the Header and Title format settings.  Quick fix, but not necessarily solving the ambiguity around both options.

2.       The Header setting is contained within the Title, meaning both sets of functionality are merged into one.  It would make the usability of a Slicer (especially from a development perspective) a lot better.

Whatever Microsoft decide to do in the future, I really hope they tidy up and fully define the Settings within Visuals, Slicer and Text Boxes.  Some things are confusing, especially to self-serve Business Analysts who rely on intuitive reporting tools.

Further Reading

Power BI Documentation –  http://bit.ly/2oKxy8V
Power BI Community Blog - http://bit.ly/2oU42OE
 

Contact Me

If you would like a copy of the workbook containing the examples or want to know more about any of the Power BI settings/properties not mentioned, please leave a comment below.  My Twitter details are also provided below.

Twitter:                                @DataVizWhizz

Themes in Power BI

Microsoft recently released Themes to a Preview version of Power BI.  The concept is pretty simple – imagine having standardized colours that can be applied to charts and matrix visuals? This is where Themes come in. The theory is this will ensure Power BI developers adhere to company policies and can do so quickly and efficiently. The reality is Themes are still lacking a lot of key configurables, but I will get onto that later.

Whilst this blog is not going to show you how to import or apply a Theme, it will demonstrate how to create one and what each configuration means. For a great high level walkthrough of the feature, click here. The aim is to not only show how this makes a developers life easier, but where it can actually be improved too.

JSON Configuration File

I have pasted the following code into Notepad++, which incorporates the Adatis branding and colour scheme.  For many developers, you will recognize the coding language – JSON (JavaScript Object Notation). If you would like to know more about JSON, check out Jason Lengstorf’s blog.

Use the below code as a template for your Theme:

{

  "name": "Adatis",

  "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4","#008DED","#74B238","#8EC954","#DB9800","#FFB60F" ],

  "background": "#FFFFFF",

  "foreground": "#002C5C",

  "tableAccent": "#81C341"

}

Notice there are a number of ‘#’ within the code.  These are Hex numbers, in which Power BI natively uses to determine a shade of colour.  This is common across many reporting/image editing tools. 

Configurations Explained

I will now explain what each line of code means and how it will effect a simple un-themed report.

 

clip_image001

1.       The name of the Theme.

2.       The colours that appear in a pre-set chart colour palette and the order of which a visual applies them e.g. Dark Blue first, Light Blue second, Green third, etc.

3.       Font colour for the matrix or table visual.

4.       Primary background colour for a matrix or table visual.  Even though the setting says’ ‘foreground’, it is more of a background colour!

5.       The table accent applies to a matrix or table visual, displaying as the grid outline or contrasting background colour, depending on what type matrix/table is applied.

 

Applying a Theme

I have created a quick Power BI report, which is using the default colour schemes applied.  The only formatting applied was to the Matrix visual and this was simply changing the style of the grid.  Here is how the report currently looks:

clip_image003

Now let’s import and apply the ‘Adatis’ theme.  You will see some of the visuals have changed, whereas other elements of the report haven’t.  I will explain in greater detail.

clip_image005

1.       The Report Title, Slicers and Titles are not affected by the Adatis theme.  This is because you cannot currently configure fonts in the JSON file.

2.       The matrix visual has updated, using the three configurations form the JSON file:

a.       background (White)

b.      foreground (Navy Blue)

c.       tableAccent (Green)

3.       The Pie and Bar chart have picked up the colours in the order they are specified in the JSON file, using the dataColors property.  However, note the colour of both data points on the Bar Chart – rather than use different colours, it uses the same primary blue colour.  Not ideal if you want to plot one colour for ‘Male’ and another for ‘Female’.

4.       The Waterfall chart hasn’t changed at all.  We would expect the Adatis colours to have been applied, but this visual seems to ignore the configuration.

We can change the Waterfall Chart manually, using the imported Adatis Colour Palette. 

clip_image007

Here is how the chart now looks:

clip_image009

What’s Next?

Whilst the concept of Themes is great, the current preview functionality is extremely limited and very much ‘Version 1’.  I am a little disappointed with how inconsistent the application of a theme is, which were highlighted in my points above.

With the majority of new Power BI features, Microsoft continue to improve the usability and functionality over time, so I am very hopeful more Theme properties will be opened up in the underlying JSON configuration file.  Options such as font colours/styles, consistent theme application (for all visuals) and company logos are all necessary for this to become really powerful.  There were talks of a CSS type of configuration in Power BI, but this has yet to announced or released.  Imagine how powerful and cool that would be?

As a BI Consultant, I am not currently comfortable with demoing Themes to a client, simply because of the clear gaps.  Once the feature is more mature and in GA, I think enterprise companies will really benefit from standardising reports across their business.  For now, Themes will remain a glorified colour palette.

Further Reading

o   Jason Lengstorf’s JSON Blog –  http://bit.ly/2aU1OHS

o   Power BI Report Themes BI Blog - http://bit.ly/2mPq69l

Contact Me

If you would like a copy of the workbook or have any questions about this blog, please leave a comment below.

Twitter:                                @DataVizWhizz

UK Power BI Summit 2017: What’s next for Power BI?

During the keynote at the UK Power BI Summit, Microsoft announced some new features coming into Power BI. Chris Webb also provided a session on Azure Analysis Services, hinting at what may be next and more importantly, how they may impact Power BI. 

Without further ado, I will dive straight in.

Power BI Visuals

Some exciting visuals were demoed by Will Thompson (Program Manager for Power BI). They were shown in an exclusive preview of Power BI, not available to the general public:

-          Themes

o   Finally! Companies can now easily ‘skin’ a suite of reports to use standardised logos, fonts, etc. 

o   The themes will be driven by JSON file.

o   All themes can be uploaded into PowerBI.com, using the ‘Themes Gallery’.

-          Slicer Improvements

o   A numeric, sliding scale slicer will be available as a new configuration option.

o   Currently only designed for attributes. 

o   Measure slicers are not  an immediate focus, but Microsoft are giving it thought.

-          Matrix/Table Visuals

o   Whilst they currently exist, a new visual will be added. This includes:

§  Drill down capabilities.

§  Include/Exclude – like what you see in charts currently.

§  Ability to highlight rows and columns, which also cross filters and highlights the selections on accompanying charts.

Analysis Services

All of the features discussed are for Azure Analysis Services, but I am sure they will also be made available on-prem soon after GA release:

-          Live Connect to Analysis Services

o   Add ad hoc measures within the Power BI Model.

o   It does not update your actual Analysis Services model, therefore, adding measures in Power BI need to be done so with caution.

o   Currently Tabular only, but should eventually come into Multidimensional too.

o   Available in both Azure (cloud) and on-prem.

-          Power Query/M Integration

o   This is the missing jigsaw to the Power BI/Analysis Services puzzle.

-          Table Level Security

o   Ability to implement this and row-level security together.

-          Migrating existing Power BI Models

o   This will be awesome! Imagine being able to move your complex Power BI model (which is now performing badly) into an Azure Analysis Services model?

o   At the moment, you have to create the tabular model again.

Conclusion

Whilst no official dates have been given, I hope that March’s release will include the visual features, such as themes. The ones specific to Azure Analysis Services are ideas that have come from many blogs (professional experts) and the Microsoft roadmap. 

If there is a feature that your customer needs or just something you believe should be in Power BI, the Ideas forum is a great place to submit your suggestion.

Further Reading

For up to date Microsoft announcements, I would definitely advise following the below blogs and twitter users:

-          Blogs

o   Power BI Ideas - https://ideas.powerbi.com/forums/265200-power-bi-ideas

o   Power BI Blog - https://powerbi.microsoft.com/en-us/blog/

o   Power BI Blog Announcements -  http://bit.ly/2kAv4Id

o   Chris Webb’s blog - https://blog.crossjoin.co.uk/

-          Twitter

o   Will Thompson (Power BI Program Manager) - @Will_MI77

o   Riccardo Muti (SSRS Program Manager) @RiccardoMuti

Contact Me

If you have any questions, please comment below or contact me directly on Twitter (@DataVizWhizz ) .

Dual KPI Custom Visual in Power BI

On February 8th, Power BI released a new custom visual called Dual KPI. The purpose of this chart is to visualise two measures over time and show their trend based on a joint timeline. The absolute values may use different scales e.g. Sales and Profit.

This blog will not only show you how to set up the new visual, but also demonstrate how changing some of the settings can enhance a report. Adam Saxton posted a YouTube video that also walks through the Dual KPI.

Pre Requisites

In order to follow my example, you will need a copy of AdventureWorksDW2014 database – found here. You will also need to download the following custom visuals:

o   Hierarchy Slicer – http://bit.ly/2kAv4Id

o   Dual KPI – http://bit.ly/2l1qCTp

NOTE:   This article assumes previous knowledge of downloading and importing Custom Visuals into Power BI Desktop. If this concept is new to you, Scott Murray’s blog gives great step by step instructions. 

Prepare Data

Open Power BI Desktop and Get Data. Point to the new AdventureWorksDW2014 database and drop down Advanced Options. Paste in the following T-SQL:

SELECT

        DPC.EnglishProductCategoryName

       ,DPS.EnglishProductSubCategoryName

       ,DP.EnglishProductName

       ,SUM([TotalProductCost]) AS [TotalProductCost]

       ,SUM([SalesAmount]) AS [SalesAmount]

       ,SUM([SalesAmount]) - SUM([TotalProductCost]) As ProfitAmount

       ,[ShipDate]

FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] FI

INNER JOIN

       [dbo].[DimProduct] DP

       ON DP.ProductKey = FI.ProductKey

INNER JOIN

       [dbo].[DimProductSubcategory] DPS

       ON DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey

INNER JOIN

       [dbo].[DimProductcategory] DPC

       ON DPS.ProductcategoryKey = DPC.ProductcategoryKey

WHERE ShipDate BETWEEN '2013-01-01' AND '2013-06-30'

GROUP BY

        DPC.EnglishProductCategoryName

       ,DPS.EnglishProductSubCategoryName

       ,DP.EnglishProductName

       ,[ShipDate]

When happy, click ‘OK’ to continue. The preview of the data will open.  Click Load, as we do not need to edit any data in the Query Editor.  Apply and changes and rename the query to ‘Internet Sales’ – final output below:

clip_image002

Some measures and attributes need to be formatted within the ‘Modeling’ Tab.

o   ‘ShipDate’ = dd MMMM yyyy

o ProfitAmout’ = Currency

o   ‘SalesAmount’ = Currency

The final formatting step is to create a Product hierarchy, based on the three product attributes.  Navigate to the Data tab, right click on the ‘EnglishProductCategoryName’ attribute and select ‘New Hierarchy’.  Drag the attributes into the hierarchy and name it ‘Products’.  It should look like the following:

clip_image005

Create Report Visual

We need to use both the Slicer and Dual KPI custom visual. To achieve this, follow the steps below:

Select the Hierarchy Slicer in the Visualizations menu and drag the ‘Products’ hierarchy on to the Fields box. The slicer will now appear in the report.

clip_image007

Select the Dual KPI Slicer in the Visualizations menu and drag the following measures to the appropriate chart properties box:

a.       ‘ShipDate’ > Axis

b.      ‘SalesAmount’ > Top values

c.       ‘ProfitAmount’ > Bottom values

clip_image009

The chart is now configured and each metric/visual is explained in more detail below. Only the top KPI (Sales Amount) is shown because both use the same calculations.

clip_image002[4]

  

1.       This is a fixed growth percentage, comparing the last (06/30/2013) vs. first (01/01/2013) data point on the graph. The metric acts as a static KPI.

2.       The Sales Amount value for the last data point on the graph. Also a static KPI.

3.       The data point currently being hovered over. This dynamically changes when you move along the axes.

4.       The Sales Amount value for the current data point being hovered over. Also dynamic.

5.       % since metric that looks at the Sales Amount for the last data point on the graph and works out the growth based on the current data point being hovered over. To use the example in the screenshot:

-          Sales Amount for 06/30/2013 = 51,596

-          Sales Amount for 05/17/2013 = 18,442

-          % since:  ((51,596 - 18,442) / 18,442) * 100 = 179.7%

Enhancing the Report

As with all custom visual in Power BI, there are lots of settings that you may never use. I have picked out some that enrich the capabilities of the Dual KPI Chart:

o   Fields

o   Warning State

§  Set alerts around data freshness and view warning messages.

o   Top/Bottom % change start date

§  For the fixed +/- % change on the chart, you can add an override start date. The dates could vary by product category and dynamically impact the % in the visual.

o   Format

o   Dual KPI Properties

§  Show abbreviated values, define multiple tooltips and show stale data warnings.

o   Dual KPI Chart Type

§  Choice of either Area or Line charts.

I have applied the Top/Bottom % change start date functionality and also formatted the chart properties. The report now looks a little more professional:

clip_image002[6]

Further Reading

o   Adam Saxton YouTube Video –  https://www.youtube.com/watch?v=821o0-eVBXo

o   Power BI Blog - http://bit.ly/2kudZ0a

Contact Me

If you would like a copy of the workbook or have any questions about this blog, please leave a comment below.

Twitter:  @DataVizWhizz