BI

ByHariharan Rajendran

Min and Max value in Power Query Power BI

Let me ask a simple question.

How to get the minimum value in Power BI? Everyone answers min() which will give minimum value.

This can be achieved in both Power Query and DAX layers.

As you know MIN(<columnname>) measure will be filtered down based on the dimension column that we use. In this post, I am explaining how to achieve the MIN / MAX value in Power Query.

Scenario – I have a below table and I want to show the minimum amount value of A & B also I want to show the original amount.

Current Data

Expected Result

A’s minimum value is 12 and B’s minimum value is 5. I want to show these numbers on A & B as below.

Let me walk through the steps to achieve an expected result.

Step 1:  use Group by ID transformation and choose “All Rows” as operation as below.

Step 2 – Create a new column with the below expression.

Table.Column([Count],”Amount”)

Here “Amount” is the column that was available before Group by. Need to provide that column name on Tabe.Column function and Count is nothing but the new column which we got as a result of the group by.

Step 3 – Either create a new calculated column or update the previous column with an additional expression as below. That expression List.Sort. The result of the step is a list. We need to sort the list in ascending order.

List.Sort(Table.Column([Count],”Amount”))

Step 4: As we are targeting for minimum value, we need to get the first value from the sorted list. Use the List.First function.

List.First(List.Sort(Table.Column([Count],”Amount”)))

The final expression will be like below.

Step 5 – Now, we will get the result as below. Remove the Count column and we will be getting only ID & Custom columns.

Step 6: Name this table and go to the main table and use merge transformation and merge with the new table using the ID column.

Step 7 – Expand the column and we will get the below-expected result.

If you want to get the maximum then use descending on the list.sort then the rest of the steps will be the same.

Happy Learning.

ByHariharan Rajendran

Power BI Report Server REST API with Postman

This post explains how to use Postman tool to access the Power BI Report Server REST API. As you know, Microsoft Power BI team released the REST API feature for both Power BI Service and Report Server. Using this REST API, we can access and control the Power BI objects.

Use the below URL to access Power BI Report Server REST API.

http://<reportservername/reports/api/v2.0/

The list of objects which supported in REST API are,

  • AlertSubscriptions
  • CacheRefreshPlans·
  • CacheRefreshPlanHistory
  • CatalogItems·
  • Comments·
  • DataSets·
  • DataSetData·
  • DataSources·
  • ExcelWorkbooks·
  • Extensions·
  • FavoriteItems·
  • Folders·
  • Kpis·
  • LinkedReports·
  • MobileReports·
  • Notifications·
  • PowerBIReports·
  • Reports·
  • ParameterDefinitions·
  • Resources·
  • Schedules·
  • Subscriptions·
  • SystemResources·
  • SystemResourceItems·
  • UserSettings·
  • System·
  • Me·
  • Telemetry·
  • PowerBIIntegration·
  • ServiceState

Microsoft providing the swagger site to show the detailed REST APIs.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/

Example,

If I want to see the list of Power BI reports then we need to use the below URL on the browser. Actually, it is a get method so we can use the browser.

http://hari-pc/reports/api/v2.0/PowerBIReports

It also will show the properties of each report.

If we want to use the other method like POST, PATCH, PUT and DELETE then we need to use any other application because it can’t be done in browsers. Use the Postman tool to access all the REST API methods.

  1. Choose Methods
  2. Place the URL
  3. Authorization
  4. Body
  5. Status

Authorization Section

 

Body Section

Happy Learning!.

 

 

ByHariharan Rajendran

Power BI REST API in PowerShell with No User Credentials pop up

In this post, I am explaining how to run the Power BI REST API based PowerShell scripts without getting a credentials pop-up window.
If you want to schedule a PowerShell file then it should not show pop-up then only it will work on windows job scheduler.
If you see the below screen, I was trying to run the PowerShell scripts and got pop-up because I used only Client ID and not passed the Username and Password.

I used the below function.
function GetAuthToken

{

if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
}
$redirectUri = “urn:ietf:wg:oauth:2.0:oob”
$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, “Auto”)
return $authResult
}

Let me show how to run the script without getting the credentials pop-up window.
We need to modify the above GetAuthToken with below script.
function GetAuthToken

{
$username = “youremailaddress”
$password = ConvertTo-SecureString “password!” –asplaintext –force

if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
}
$redirectUri = “urn:ietf:wg:oauth:2.0:oob”
$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”
$credentials = New-Object System.Management.Automation.PSCredential $Username,$password
$AADcredential = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $credentials.UserName,$credentials.Password
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId,$AADcredential)
return $authResult
}

In the above script, I have introduced 2 variables $username and $password and hard-coded the credentials on the script itself.
Again another set of variables to handle the username and password $Credentials & $AADCredentials.
Use the above code on your script and it will not ask pop-up any window. You can use the PowerShell script file on the job scheduler.

Happy Coding!!!

ByHariharan Rajendran

DAX – Adjust the Total value – Conditional Total in DAX

In this article, I want to show you how I achieved a business solution using DAX. Let me explain the requirement.

I have a below table and customer wants the data in the below format.

Country Color Quarter Product
India Green 2019-Q1 P1
USA Blue 2019-Q1 P2
UK Red 2019-Q1 P3
India Red 2019-Q2 P1
USA Blue 2019-Q2 P2
UK Amber 2019-Q2 P4

 

Expected Solution

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 100% 0%
USA 100% 100%
UK 0% 0%
Total 67% 33%

 

Actually, the customer wants to show the count as 1 or 0 if green or blue exists on each country so the required result would be like below.

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 1 0
USA 1 1
UK 0 0

 

First step: we can target blue and green count.

Green_Blue = CALCULATE(

COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

The next upgraded version is handling blank with zero.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal)

Next level, we can divide the value to get the percentage.

GB =

Var GBVal  = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal  = BLANK(),

0,

GBVal  / CALCULATE(

COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])))

Then finally, we need to tweak the result on the divide section to get the expected result. This will adjust the total section.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal / if(

DISTINCTCOUNT(‘Product'[Country])>1,

CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])),GBVal))

We got the expected result. Please provide your feedback and comments.

 

ByHariharan Rajendran

Power BI Shared Datasets – Cheat Sheet

In this article, I want to show you the cheat sheet of the Shared Dataset inside Power BI. I tried to cover all the information related to shared dataset.

As you know, the Shared dataset is one the latest update from Power BI team which opens a new channel to data analyst & business users to create reports from the available datasets. It reduces their work on building the data model from scratch.

It also helps the organization to maintain a single version of the truth on data.

Please share your feedback and comments below.

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