Power BI Direct Query Limitation

To use direct query mode, select your supported data source and then DirectQuery: Click OK and you will then be able to select your tables: From here, you can work with the data source by adding new custom columns, measures and calcuated columns and define new relationships from the connected data and also other data sources. You will have a limitation on additional Direct Query data sources. If you add a new … Continue reading Power BI Direct Query Limitation

Getting Data Excel 2016

Excel 2016 has built in BI capabilities. To get data from an external data source, go to the Data tab: Under Get and Transform, select New Query: We will get data from a local SQL database: Enter the database details: Select a table and click Edit: This opens the Query Editor: Click the Advanced Editor to see the connection details: Click OK to close the Query Editor. The data is loaded to the … Continue reading Getting Data Excel 2016

Dynamics 365 Solution Templates Install – Sales Management

To install the Sales Management for Dynamics 365 Solution Template, go through the following steps. Go to https://powerbi.microsoft.com/en-us/solution-templates/dynamics-crm/ Click Install Now: Select the instance of Dynamics 365, on premise or online and click next: Click Connect: Click Accept: If you do not have an Azure account, you will see this message: Sign up and click Connect again. Select the organization and the subscription: Pricing calculator: https://azure.microsoft.com/en-us/pricing/calculator/ Estimated costs (link not working but … Continue reading Dynamics 365 Solution Templates Install – Sales Management

Power BI and Local Excel Data Refresh

Here we will cover the scenario where there is a local spreadsheet file on a user’s desktop, and we have a Power BI desktop file that connects to the spreadsheet. We want that data to be automatically updated in the Power BI service. To do this, first create a new Power BI desktop file and connect to the spreadsheet. Here is our excel file: In Power BI Desktop, go to Get Data->Excel: Create … Continue reading Power BI and Local Excel Data Refresh

Connecting Dynamics 365 and Power BI with Microsoft Flow

In my previous post, I discussed my prototype to show streaming live sales orders that are being entered into Dynamics 365 appear in a Power BI dashboard. Here, I will go into details on how to set this up. First step is to create a streaming data set in Power BI. Log into powerbi.com. Select Streaming Datasets from the bottom left of the screen. Select from the top right Steaming datasets: Here, … Continue reading Connecting Dynamics 365 and Power BI with Microsoft Flow

Watch Your Sales Orders Stream in Real Time with Dynamics 365 with Flow

Here’s the scenario. You’re a CEO, and you love seeing new sales orders stream in. And you love the beach. You want to see these orders visually appear in your system while you are sitting on the sand. Now imagine being able to achieve this today, without anyone having to write a single line of code. That’s what Microsoft has made possible with the new cloud based automation service called Flow, together … Continue reading Watch Your Sales Orders Stream in Real Time with Dynamics 365 with Flow

Power BI – Union in DAX

In Power BI, through DAX we have the ability to combine 2 tables. We can do this using the UNION function. In Power BI Desktop, select New Table: We have 2 existing tables, East Region Sales and West Region Sales, with the same number of columns. We will combine the two in a Sales table. You will see: Add the code: The combined table shows data from both tables:  

Power BI Dynamics 365 Content Packs Source Code

There are 3 Dynamics 365 content packs for Power BI that Microsoft has provided the PBIX files for download. These files are opened with Power BI Desktop and can be modified and then uploaded to your Power BI environment. To download the PBIX file source code, go to: https://technet.microsoft.com/en-us/library/mt490470.aspx: Select the links to download: From here you will have the source, which you can then customize:  

Install Power BI Dynamics 365 for Sales Content Pack

To install a Dynamics 365 Content Pack for Power BI, go through the following steps. First, log into powerbi.com. Then, select the Get Data at the bottom of the screen: This will open the page below: Select under Services Get. Search for Dynamics: Select the Microsoft Dynamics 365 Online Sales Manager. Click Get It Now: Enter your Dynamics 365 URL and Fiscal Year End Month Number. Note the trailing / … Continue reading Install Power BI Dynamics 365 for Sales Content Pack

Power BI Service and Importing and Uploading Excel Spreadsheets

Here we will look at how using Excel spreadsheets as datasources works with Power BI. Let’s start with a spreadsheet we have stored locally on our server. The file is called ExcelTransactions.xlsx and is stored locally. It contains rows of data (that have been typed into the spreadsheet and are not external data sources): In PowerBI.com, select Get Data->Files->Get: Select Local File: Select our ExcelTransactions.xlsx file. We will now see this page: Select … Continue reading Power BI Service and Importing and Uploading Excel Spreadsheets