Tag: Power BI

Power BI, Power BI Service

Data Classification in Power BI

Power BI Data ClassificationIn many corporations depending on the type of data is being used there could be different types of the sensitivities that should be applied to that data. Data Classification fulfills in Power BI Service this matter very easily. In today’s post you’ll learn how to setup Data Classification in Power BI Service.

First of all I want to inform you that Data Classification is NOT a sort of security or privacy setting. It is only a TAG which is all about informing Power BI users across a corporation to take extra care when they want to share data with other people inside or outside of that corporation. For instance some data might be OK to be shared externally outside the company, but, the other data might not be shared with groups of people even within that corporation.

Depending on your corporation you might have different levels of sensitivity like

  • High Sensitive Data
  • Medium Sensitive Data
  • Low Sensitive Data

So depending on what level of sensitivity, for instance for High Sensitive Data, the Power BI users should be really careful of who they share Power BI Dashboards and data with. In Power BI Service we can easily setup data classification on our dashboards so anyone who is looking at that dashboard is able to understand how sensitive that dashboard is and who they can share it with.

Requirements

To be able to setup Data Classification in Power BI Service you have to:

In case that you want to add another admin user,and if you already integrated your on-premises Active Directory with Azure Active Directory (AD) then you can either grant necessary admin rights to that user from your Azure portal in Azure AD or directly from Office 365 Admin Centre.

The user needs to be an Office 365 “Global Administrator” to be able to setup data classification in Power BI Service. A global administrator will have access to “Admin Portal” panel within Power BI Service which includes data classification and many more other important settings.

Make a User Global Administrator in Office 365

After you signed into your Power BI Service account,

  • Click “Admin” tile from the app launcher

Office 365 Admin Centre

Continue reading “Data Classification in Power BI” →

Power BI, Power BI Desktop

Power BI Desktop Query Parameters, Part 3, List Output

List Output in Power BI Query Parameters

In the previous posts, here and here, I explained how you can use Power BI Desktop Query Parameters for many different use cases. Power BI development team added another cool feature to Power BI Desktop on July 2016 which is the ability to add a List Query output to a query parameter as it’s “Suggested Values” (formerly “Allowed Values”). This feature is very useful and from now on we are not restricted to proviode a static list of values in “Manage Parameters”. In this post I show you how to use a list output in query parameters.

Note: This feature is NOT available in DirectQuery mode at the time of writing this post.

Requirements

In this post as usual I’ll connect to a SQL Server database as a sample. To be able to follow this post you have to have:

  1. The latest version of Power BI Desktop (current version is 2.38.4491.282 64-bit (August 2016))
  2. AdventureWorksDW

Scenario

In the first post of these series I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. But, what if we want to filter query results based on the values of a column from a particular table? Previously we couldn’t answer these sort of questions if we want to filter FactInternetSales based on a selected values of EnglishProductName column from DimProductCategories using Query Parameters. But, now we can easily implement those sort of scenarios.

Let’s implement this scenario.

Loading Data into the Model:

  • Open Power BI Desktop
  • Get data from SQL Server and connect to Adventure Works DW 2016 CTP3
  • Select “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click “Load”

Power BI Desktop Loading Data into the Model

  • Switch to “Relationships” view to make sure the relationships detected correctly then click “Edit Queries” from the ribbon

Power BI Desktop Edit Queries

Continue reading “Power BI Desktop Query Parameters, Part 3, List Output” →

Excel, Power BI, Power BI Publisher for Excel, Power BI Service

Analyse Power BI Data in Excel

A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.

Requirements

  • Desktop versions of Microsoft Excel 2007 and later
  • Download and install Power BI Publisher for Excel
  • Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.

Enable Power BI Publisher for Excel

Connect to and Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)

Previously we could analyse Power BI data in Excel directly from Power BI service by:

  • Log in to Power BI Service
  • Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”

Analyse Power BI Data in Excel from Power BI Service

  • Clicking ellipsis button of a desired report and clicking “Analyse in Excel”

Analyse Power BI Reports in Excel from Power BI Service

  • Doing either way, it downloads an “odc” file that could be opened in Excel.

Analyse Power BI Data in Excel from Power BI Service Enable Data Connection

  • Now you can analyse the data in Excel using pivot tables and pivot charts.

Analyse Power BI Data in Excel

Continue reading “Analyse Power BI Data in Excel” →

DAX, Excel, Power BI, Power BI Desktop, Power Map, Power Pivot, Power Query, Power View, Self-Service BI, T-SQL

How to Overcome Map Related Issues in Power BI, Power View and Power Map

One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.

Requirements

To experiment everything I explain in this post you need to have:

  • The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
  • The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
  • Excel 2016 or Excel 2013

If you use Excel 2016, then you need to turn on Power View on.

Check this out if you want to learn more about BI features in Excel 2016.

Get Data in Power BI

  • Open Power BI Desktop
  • Get Data from SQL Server Database
  • Select Fact.Sales and Dimension.City then load data

Power BI Desktop

Map Issues In Power BI

Wrong Cities in Power BI

  • Expand the “Dimension City” table
  • Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)

Power BI Desktop Data Category

  • Put a Map visual into the page
  • Put “City” on Location
  • Put “Total Excluding Tax” on Size

Power BI Desktop Map

As you see sales distributed across different countries, but, this is not quiet right.

  • Put a slicer on the page then put “Country” on the slicer
  • Click “United States” to filter the Map

Power BI Desktop Slicer

Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.

Continue reading “How to Overcome Map Related Issues in Power BI, Power View and Power Map” →