Power BI

ByHariharan Rajendran


This article explains how to use SAMEPERIODLASTYEAR function in SSRS to achieve the same result as like in Power BI.

The challenging part is we don’t have SAMEPERIODLASTYEAR function in SSRS so we need to look for another function which should give the same result.

We have “Previous” function in SSRS, we are going to use that function to achieve the result. Let us get started.

First, let see the result of Power BI – SAMEPERIODLASTYEAR



Let us replicate the same result in SSRS.

First, understand the functionality of “Previous” function.

It will give the result of the previous row as the line below.

We need to tune the previous function to get the result as same as SAMEPERIODLASTYEAR in Power BI.

Follow the below steps.

  1. Create a Parent Group called Year using “Year” Column
  2. Create a Child Group called Month using “Month” Column
  3. Create a child group called “Day” using “Day” column.

  1. Change the previous function expression as like below.



  1. That’s it. We achieve the result as same as Power BI.



The result of the previous function will work only on the current data displayed. For example, if you create a parameter for year and filter for the year 2019 then it will not show any value for previous value.

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



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(


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])




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>”



    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″)




        start_time = time.time()

        for i in range(1):

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











        end_time = time.time()


        run_time = end_time – start_time

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


except KeyboardInterrupt:





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.


ByDr. SubraMANI Paramasivam

Embed Face API Results in Power BI

As you know, the result of any APIs from Azure Cognitive services is a JSON file. The structure of the JSON file is not in a proper way to handle them effectively and easily inside Power BI.

In this article, I am explaining the easiest way to get the result in a proper way inside Power BI.

To accomplish the result, I am using a python script. As Power BI starts support python as one of the data sources we can easily pass the python script and get the API result.

Azure Cognitive services have a bunch of APIs and documentation and API reference for each API. Since I am using python script, I can easily get the python Face API reference and use it directly.


  1. Python 3
  2. Power BI Desktop

Use the below python code. Update your Face API subscription key & url.

from urllib.request import urlopen

import json, os, io, requests

from io import BytesIO

import pandas as pd

subscription_key = "your_subscription_key"
base_url = "https://your_region.api.cognitive.microsoft.com/face/v1.0/"

headers    = {'Ocp-Apim-Subscription-Key': subscription_key,

              'Content-Type': 'application/octet-stream'}

params     = {'returnFaceId': 'true',

    'returnFaceLandmarks': 'false',

    'returnFaceAttributes': 'age,gender,smile,facialHair,headPose,glasses,emotion,hair,makeup,accessories,blur,exposure,noise'}


with urlopen(Image_Path) as url:

    image_data = io.BytesIO(url.read())
    response = requests.post(

          detect_url, headers=headers, params=params, data=image_data)

    smile= [face[0]['faceAttributes']['smile']]

    gender = [str(face[0]['faceAttributes']['gender'])]

    age= [face[0]['faceAttributes']['age']]







    neutral = [face[0]['faceAttributes']['emotion']['neutral']]







    face_ds = pd.DataFrame({

        "smile": smile,

















You can test the above code on your python IDE and can see the result which will be in a table format.

Power BI Desktop Report

Follow the below steps.

Open Power BI Desktop and choose “Python script” as a data source.

Copy and paste the above code on the editor window.

Click ok and it will load and display the table as like below.

Load the data and you can use those fields on your report.

As of now, the image path is hardcoded by you can dynamically pass it by using the parameters.

The sample look and feel of the report.

ByHariharan Rajendran

PowerBI – Python Support

Microsoft introduced Python support from Power BI on August 2018 update.

PowerBI is supporting R visuals and PowerBI producing custom R visuals too. PowerBI team extended the data science capability of PowerBI by supporting python.

In this post, I am explaining the simple python plot in Power BI.

Download the latest version of Power BI. As python support in preview support, we need to enable the python support preview feature.


You should install the Python on your machine so that you can point the python home directory which is mandatory to run any python scripts inside PowerBI.

Once you have configured the setup then you need to test with simple code. Use the below code.

import matplotlib

import numpy as np

import matplotlib.pyplot as plt

x = np.linspace(0, 3*4, 500)

plt.plot(x, np.sin(x**2))

plt.title(‘Test Plot’)



The above code works perfectly and produced the plot on jupyter notebook and need to get the same result on PowerBI also.

Drag and drop the python visual into the canvas area. In the below script editor, reference any dummy column and then copy and paste the above script.

Finally, execute the script and it will produce the plot as like below.


Now, you can play with your python scripts.

ByHariharan Rajendran

DAX – RelatedTable Scenario

There is a scenario to find the total count of vendor based on the current year and the same customer should exist on the main transaction table.

Let me explain in detail.

Consider, I have a table called vendors which have very simple columns like below.

Vendor ID Vendor Name Created Date
101 V1 Wednesday, February 22, 2017
102 V2 Thursday, December 28, 2017
103 V3 Friday, February 2, 2018
104 v4 Monday, May 7, 2018


And Transaction table like below.

Trans ID Vendor ID QTY Sale Amount OrderDate
1 101 5 1500 Tuesday, August 22, 2017
2 101 6 2588 Saturday, December 30, 2017
3 102 8 4500 Monday, April 2, 2018
4 103 12 8000 Thursday, June 7, 2018


On the above tables, I want to show the total count of vendors where they are available in the transaction table and their created date should be the current year.

The expected result is “1” as Vendor 103 only available in Transaction table and created date is the current year 2018.

Rest of the rows are not matched with this scenario.

There are various methods are available to achieve the solution. In this post, I am explaining the very simple method.

Step 1: Create a new calculated column on the table Vendors using below script.

IsMatch = IF(Format(TODAY(),”YYYY”) = FORMAT(‘Customer (2)'[Date],”YYYY”), “Match”, “NoMatch”)

It is adding a new column with values like match and nomatch.


Step 2: Create another calculated column on the vendor table using below script. We need to use the above-created column.

Count = CALCULATE(IF(ISBLANK(MINX(RELATEDTABLE(‘FACT’),”1″)),0,1),’Customer (2)'[Test]=”Match”)

That’s it. We can use the count column for the result.