Adatis

Adatis BI Blogs

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. Click ‘Add’ and the PowerApps Custom Visual is available. 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:  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. Click ‘Choose App’, select the PowerApp and click ‘Add’. That’s it, the PowerApp is now embedded and ready to use in Power BI Desktop. 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: Principal A ‘Target’ value is changed from 850000 to 950000.   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.   After: Published Report The report can now be published to the Power BI Service. 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

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: 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: 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. 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 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.    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: 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