BI

ByHariharan Rajendran

Power BI Embedded Service Principal – Postman

Microsoft Power BI team released a new update recently on Power BI Embedded to use service principal whenever it required. In this article, I am explaining that how to add service principal into Power BI service workspace which is not a direct way.

Service principal should be added into Power BI using REST API. Follow the below steps.

First create Power BI new workspace.

Add the service principal as a user with admin access. To perform this step, we need to use Power BI REST API.

I have used postman to complete this step.

First, I generated the authorization token. POST https://login.microsoftonline.com/common/oauth2/token?

Then added the service principal into the Power BI workspace.

 

ByHariharan Rajendran

Dataset Refresh in PowerApps Custom visual

As we know, Power BI can be integrated with Power BI. Check the below approaches.

  1. In Power Apps, we can use Power BI tiles
  2. In Power BI, we can use Power Apps custom visual.

 

This article explains the latest update to the Power Apps custom visual. Let us take a scenario.

I need to embed the Power Apps with in Power BI report. Whenever an item is added through PowerApps, I need to refresh the dataset used for the Power BI report.

For the above scenario, we need to use Microsoft Flow to achieve the result. In Microsoft flow, we need to have custom connector to refresh the Power BI dataset.

From the latest update from PowerApps custom visual, we no need to go to the above step. I mean, we no need to use Microsoft flow to refresh the dataset. We have refresh method added with Power BI integration data source itself.

It is a very good feature for Power BI + PowerApps scenarios.

ByHariharan Rajendran

DAX Group by and Filter

This post explains you that how DAX can be used for Group by and Filter logic. To explain this scenario, I am taking an example data.

Example Data:

I have a following table.

EmpID EmpDesignation EmpDepartment DesigFrom
1 A IT – Finance 01/02/2018
1 SA IT – Sales 05/08/2018
2 M Marketing 05/07/2018
2 SM HR 25/08/2018
3 A Sales 06/06/2018

 

Requirement

As per the requirement, I need to perform below actions.

  1. Date Slicer should be available to filter From and To date
  2. Based on the filter, a table of figure should display the latest designation.

Expected Result

Filter – Start Date: 01/02/2018 to 31/08/2018

EmpID EmpDesignation MostEffective
1 SA 05/08/2018
2 SM 25/08/2018
3 A 06/06/2018

 

To achieve the above result, we need to create a DAX script. Use the below DAX script

MosftEffectiveDate = CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation] = EARLIER( Employee[EmpDesignation])))

 

Sometimes, you may receive error due to Earlier function. Check the another version of the script without Earlier.

MosftEffectiveDate =

VAR Desig = MAX(Employee[EmpDesignation])

RETURN

CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation]=Desig))

 

ByHariharan Rajendran

Python Script to Event Hub – Power BI Sentiment Analysis ML Function

This article explains the python script to send the data to event hub.

As we know, event hub is azure service which is designed to capture the streaming events from different devices or applications.

The below python script sends the sentiment string to event hub.

import sys

import logging

import datetime

import time

import os

import itertools

from azure.eventhub import EventHubClient, Sender, EventData

 

logger = logging.getLogger(“azure”)

 

ADDRESS = “amqps://<eventhubworkspacename>.servicebus.windows.net/<eventhubname>”

 

# SAS policy and key are not required if they are encoded in the URL

USER = “RootManageSharedAccessKey”

KEY = “<Key>”

 

try:

    if not ADDRESS:

        raise ValueError(“No EventHubs URL supplied.”)

 

    # Create Event Hubs client

    client = EventHubClient(ADDRESS, debug=False, username=USER, password=KEY)

    sender = client.add_sender(partition=”0″)

    client.run()

   

    try:

        start_time = time.time()

        for i in range(1):

            print(“Sending message: {}”.format(i))

            a=”[{“+”ts”+”:”+str(1550321522)+”,”+”Message”+”:”+”\”good\””+”,”+”TestVal”+”:”+str(7)+”}]”

           

       

            print(a)

            sender.send(EventData((a)))

                     

           

    except:

        raise

    finally:

        end_time = time.time()

        client.stop()

        run_time = end_time – start_time

        logger.info(“Runtime: {} seconds”.format(run_time))

 

except KeyboardInterrupt:

    pass

 

 

Applications

The above script sends the data to event hub which can use used as an input to the AZ ML function in Stream Analytics.

The output of the stream analytics query can be used for Power BI streaming dataset.

 

ByHariharan Rajendran

[Solved] Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

This post helps you solve the below issue in Power BI.

Scenario: There will be an error when we connect with 32-bit Access Database from Power BI 64-bit desktop.

Error: “Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”.

Solution

To solve the above issue, follow the below steps.

  1. Visit https://www.microsoft.com/en-gb/download/details.aspx?id=13255
  2. Download 64-bit version of the software.
  3. Install on the machine where Power BI is installed.

Once completed the above steps, try to connect access DB from Power BI desktop. This time you will be able to connect without an issue.

Post your comments if you get any more issues in this scenario.

ByDr. SubraMANI Paramasivam

Empowering Every Person in the planet, with “Awareness Enabled Reports”

Following Satya’s quote “Empowering Every Person in the Planet”, in 2016 Microsoft Inspire conference, have certainly left my mind with unstoppable beats. I certainly understood what Satya meant, but I still thought about doing something like this, with my own upgraded version of Data Awareness Programme (which I started in 2014). But at that time, all I had was Power View, Power Map, Power Pivot as Excel add-ons and tried my best, to do the awareness programmes in remote villages, by mingling with the villagers and collecting their own data and showing some visuals back to them. I did this to improve their life style, find more time for personal and have better earnings. Though the main target was students, I hoped this message would spread to their friends, family and others in the remote places.

Following the release of full version of Power BI, I now have a fully working site, with living “Awareness Enabled Reports”, from sleeping open data sources (taken from various Gov/Non-Gov sites).

I managed to get this far, with a simple equation of A + B + C + D = E (EmpoweringEveryPerson.com (EEP)). Let me explain this in detail.

Following this, I managed to extract data from various open data sources and identified the most global challenges, that we are encountering and/or going to be a major threat in near future.

With our all time favourite reporting tool, “Microsoft Power BI“, published some “Awareness Enabled Reports” to www.EmpoweringEveryPerson.com site and categorized them with regional, national and global challenges, for easy manoeuvring within the EEP site.

 

This EEP site currently has 3 simple goals.

  1. View the “Awareness Enabled Reports” worldwide in any devices, by categorizing Regional or Global challenges.
  2. Submitting another “Awareness Enabled Report” as a Developer with some guidance. Also listed, Global Challenge Topics to select from open data sources and provided some tips to convince the selection committee and finally submitting the story.

  1. Promote EEP
    1. Provided options to Promote EEP as  a Developer, User Group Leader & End user.

Below screenshot shows categorization of reports by UK.

 

PROMOTE & PARTICIPATE

As per above equation, ‘D’ is the support that we need from you, to promote in any of the following ways.

1. DEVELOPERS

Develop “Awareness Enabled Reports” for all listed Global Challenge topics and submit your data story to EEP site and once published, tweet / share / post in social media and spread the awareness.

2. COMMUNITY LEADERS

A request to all Community group leaders, to spend at least a minute by starting or ending your user / local / online group sessions by introducing / re-introducing, this website and showcasing the opportunity to all attendees, to build and submit their own data story with “Awareness Enabled Reports“.

3. END USERS / VOLUNTEERS

Every time you see a new “Awareness Enabled Report“, do tweet / share / post in social media and support to spread the awareness.

 

Thanks in advance for your support and thanks for your time reading through this far, to create awareness with “Awareness Enabled Reports“.

ByHariharan Rajendran

Power BI Bookmark and Selection

Microsoft Power BI recently released new features as part of October release. In that, the below two features are very important to consider for our reporting solutions.

  1. Bookmarking
  2. Selection Pane.

First, let me explain the selection pane feature. It is useful to show and hide any report elements in the report.

If we see selection pane alone, then we can’t identify the importance of that but, this selection pane will combine with the bookmark and do magic.

Please check the below report and play with different chart types.

https://app.powerbi.com/view?r=eyJrIjoiN2Q3Yjg3OTUtMDM4NC00ZTU5LWE5MjAtNGVjODMxOGJiOWQzIiwidCI6IjNkMWQwNTA0LTRjNDItNGUwMi1hZTI4LTVjOWE5YzUwZjM2ZiIsImMiOjh9

Take your time and think that how this report works. Actually, it is a simple when you see the report but in the backend it using the selection and bookmark features.

Before explaining how the report is built, let me explain what bookmark feature is.

Bookmark

It is a preview feature which means it is not generally available. Microsoft Power BI team announced this feature during Data Insight Summit. They have created a hype to the feature.

We can create a bookmark for the interesting stats. For example, if you want to create a data story or you want to present the report to the business users to show the sales and revenue information.

At that time, you may need to show metrics for last year sales and this year sales for comparison and same for revenue or you may need to highlight a specific visual (it can be done with spotlight, refer here).

With the single report you need to filter the data during the presentation but instead, you can create a bookmark for each one of the results and can show them easily.

Learn more about Bookmarking feature in Power BI site.

Let me explain how the report is built with the help of bookmark and selection pane. Follow the below steps to reproduce the report.

  1. Create a report with pie charts
    1. Create a bookmark as pie
  2. Add the donut charts on the same page where pie charts are placed
    1. Create a bookmark as Donut
  3. Add column charts on the same page where pie and donut charts are placed
    1. Create a bookmark as Column
  4. Open the selection pane and hide the donut and column charts and update the pie bookmark
  5. Choose Donut bookmark and hide pie and column charts and update the bookmark
  6. Same for column bookmark
  7. Add the slicer on top and added three images (pie, donut & column chart icons)
  8. On each image, on Link option choose bookmark type and select the relevant bookmarks

That’s it. Please let me know your comments below also share if you have any other report logics with these features.

ByHariharan Rajendran

Power BI Spotlight

It is one of the features recently released by Microsoft Power BI Team.

Actually it a very simple feature but more effective. You can check the spotlight for any visuals or elements that you added in your report.

You can see this option when you click “…” (Three dots) on any visual’s top right side.

It enables you to highlight only the specific report element from your report. During the presentation, if you want to highlight any visuals or elements then spotlight can be used.

It also can be added as a separate bookmark. Refer bookmark feature in Power BI.