Monthly Archives:September 2018

<
ByHariharan Rajendran

DAX – Top N for each Previous & Current Year

This article covers a real-time scenario which was asked by one of the customers.

Requirement:

Show the sales of the top 5 countries for the current year and previous year.

Challenges:

The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.

  1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
  2. We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.

Solution:

Follow the below steps

  1. Create a DAX table with below logic
    1. TOP 5
    2. Filter – Current Year & Previous Year
    3. Union the above results

Detailed Steps:

I am using below dataset (Sales).

Country Year Value
A 2018 12
B 2018 34
C 2018 56
D 2018 17
E 2019 17
F 2019 19
G 2019 11
H 2019 23
I 2017 16
J 2017 18
K 2017 23
L 2017 26
M 2017 56

 

Used the DAX syntax to create another table

_Top5 = UNION(

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))

)

Now, create a stacked column chart which will give the expected result.

post your comment, if you have any other approaches.

ByHariharan Rajendran

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

  1. List of Workspaces
  2. List of Datasets
  3. List of Reports
  4. List of Dashboards
  5. List of Tiles
  6. List of Data Gateways
  7. List of Data sources on the gateway
  8. List of users and their access.

 

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

  1. Register an app and get the Client ID and Use it on the below script

 

[sql]

# Parameters – fill these in before running the script!

# ======================================================

 

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

 

$clientId = “xxxxxx-xxxx-xxx-xxx-xxx”

 # End Parameters =======================================

 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

}

 function get_groups_path($group_id) {

    if ($group_id -eq “me”) {

        return “myorg”

    } else {

        return “myorg/groups/$group_ID”

    }

}

 # PART 1: Authentication

# ==================================================================

$token = GetAuthToken

 # Building Rest API header with authorization token

$auth_header = @{

   ‘Content-Type’=’application/json’

   ‘Authorization’=$token.CreateAuthorizationHeader()

}

 # ==================================================================

# Get the list of groups

$uri = “https://api.powerbi.com/v1.0/myorg/groups/”

$all_groups = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = (“C:\Users\Hari\Documents\Allgroups.csv”)

$all_groups | Export-csv $filePath

 # ==================================================================

# Get the list of gateways

$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = (“C:\Users\Hari\Documents\AllGateways.csv”)

$all_gateways | Export-csv $filePath

 # ==================================================================

# Get the list of gateways data sources

$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

$all_gateways_id=$all_gateways.id

 foreach ($gateway in $all_gateways_id)

{

     $uri = “https://api.powerbi.com/v1.0/myorg/gateways/$gateway/datasources”

   $all_gateway_DS = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

  }

$filePath = (“C:\Users\Hari\Documents\AllGatewaysDS.csv”)

$all_gateway_DS | Export-csv $filePath

 # ==================================================================

# Get the list of datasets from Groups

$all_datasets=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets”

   $datasets = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_datasets+=$datasets

  }

$filePath = (“C:\Users\Hari\Documents\alldatasets.csv”)

$all_datasets| Export-csv $filePath

 # ==================================================================

# Get the list of reports from Groups

$all_reports=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/reports”

   $reports = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_reports+=$reports

  }

$filePath = (“C:\Users\Hari\Documents\allreports.csv”)

$all_reports| Export-csv $filePath

 # ==================================================================

# Get the list of dashboards from Groups

 $output_dashboard = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards”

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   Group_Id = $group.id

   GroupName = $group.name

   displayName = ($all_dashboards.displayName -join ‘,’)

     } | Select-Object Group_Id,GroupName, DisplayName

     }

$filePath = (“C:\Users\Hari\Documents\alldashboards.csv”)

$output_dashboard| Export-csv $filePath

 # ==================================================================

# Get the list of tiles

$all_tiles=@()

foreach ($group in $all_groups)

   $group_id = $group.id

    $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards”

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   foreach ($dashboard in $all_dashboards)

   {

     $dashboard_id = $dashboard.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards/$dashboard_id/tiles”

   $tiles = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   }

   $all_tiles+=$tiles

  }

$filePath = (“C:\Users\Hari\Documents\alltiles.csv”)

$all_tiles| Export-csv $filePath

 # ==================================================================

# Get the list of users from Groups

 $output = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/users”

   $all_group_user = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   GroupName = $group.name

   Access = ($all_group_user.groupUserAccessRight -join ‘,’)

   DisplayName = ($all_group_user.displayName -join ‘,’)

   PricipalType = ($all_group_user.principalType -join ‘,’)

   } | Select-Object GroupName,Access, DisplayName, PricipalType

     }

    

$filePath = (“C:\Users\Hari\Documents\AllUsers.csv”)

$output | Export-csv $filePath

 ## ==================================================================

## Get the list of datasets from Groups with Group Names

 

$alldatasets_GP = foreach ($group in $all_groups)

   $group_id = $group.id

  $group_name = $group.name

   $uri = “https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets”

   $alldatasetsGP = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

       New-Object -Typename psobject -Property @{

   GroupId = $group.id

   GroupName = $group.name

   Dataset_Id = ($alldatasetsGP.id -join ‘,’)

   DisplayName = ($alldatasetsGP.name -join ‘,’)

     } | Select-Object GroupName,Dataset_Id, DisplayName

 }

$filePath = (“C:\Users\Hari\Documents\alldatasets_Groups.csv”)

$alldatasets_GP | Export-csv $filePath

[/]
ByHariharan Rajendran

Recover & Restore Deleted Power BI Workspace (Step by Step)

In recent times, I encountered one of the most common questions is how to recover the Power BI workspace and how to restore the deleted workspace in Power BI?.

I thought it would be very helpful if I write a blog post to answer those questions. This content is applicable for Power BI new workspaces.

Recover and Restore are two different concepts. Let us try to understand the difference first.

Restore – If you deleted an object which you want to get it back

Recover – The live object went to not responding stage or orphaned stage and getting that object back to live.

In Power BI,  Most of the time, you will encounter restore scenarios.

To perform the restore and recover operations, you need admin rights in Power BI.

Restore Operation

Let us consider a scenario, one of your team member working on maintenance activity and unfortunately, he deleted one of the business app workspaces as an admin you want to restore it back.

Follow the below steps

Step 1: Log in to app.powerbi.com and head over to admin portal

Step 2: Check for workspaces section

Step 3: Check for the workspace on the list and check the State of the workspace.

Step 4: Choose the deleted workspace

Step 5: You can see an option called “Restore” on the top. Click Restore.

Step 6: Add at least one user as an admin and click restore. Now, the workspace will be restored.

The contents on the workspace also will be restored.

 

Recover Operation

To understand recover workspace, we need to know the different states of the workspace. Usually, we will be seeing the following states Active & Deleted.

Active – The workspace is live, and users can access the content from the workspace

Deleted – The workspace is deleted and can be restored if you want. Once restored then workspace will change to Active state.

Other than the above we have another state called “Orphaned”. If the workspace is changed to orphaned then you to recover workspace otherwise it will not visible to the users and can’t access that workspace.

Let me explain to you how to make the workspace orphaned but you don’t try at your end.

Whenever you create a workspace, it will consider the workspace creator as an admin of the workspace. It is always mandatory that at least 1 user should be admin of the workspace. You can check the access once the workspace is created.

Sometimes you might encounter the below error message. It means you or your admin team member removed the admin of that workspace.

If any workspace doesn’t have an admin then it will change to the orphaned state.

In Power BI, you can’t remove the admin from the workspace but there is an option to remove an admin user from the workspace,

 

I don’t know why the product has this option. As per my understanding, this should be fixed. Let me show how to remove the admin users as well. Please don’t try with your production workspaces.

 

Follow the below steps

Step 1: Log in to app.powerbi.com and head over to admin portal

Step 2: Check for workspaces section

Step 3: Check for the workspace on the list and check the State of the workspace.

Step 4: Choose the orphaned workspace

Step 5: Click recover on the top. Recover option will be available only for orphaned workspace

Step 6: You need an admin user to the workspace, and it will be recovered.

Thanks

Please send your feedback.

ByHariharan Rajendran

Microsoft Flow – Find Min or Max Value

This post explains the steps that you need to perform to find the minimum or maximum value from a list of values or a field from a table in Microsoft flow

I am explaining a simple procedure to achieve the result.

Procedure:

1.       Take the first number and store it on a variable

2.       Compare that variable to all the values from the field

3.       Check the Maximum (greater than) value – If else condition

Scenario:

I am taking google sheet which has the column called “Sales”. It has some value. I need to find the maximum value.

Follow the below steps in Microsoft flow

Step 1 – Use any trigger

Step 2 – Action – “Get Rows – Google Sheet” and select the appropriate sheet.

Step 3 – Action – “Select”

        From : “Record value”

        Map : “Sales” => Sales

Step 4- Action – “Initialize Variable”

        Name : “MaximumSales”

        Type: “Integer”

        Value: int(first(body(‘Select’))?[‘Sales’])

Step 5 – Action – “Apply to each “

         Select an output from previous steps: Records value

        Condition

                                                               i.      int(items(‘Apply_to_each’)?[‘Sales’]) “is greater than” “MaximumSales”

                                                             ii.      If Yes

        Set Variable

               Name: MaximumSales

               Value: int(items(‘Apply_to_each’)?[‘Sales’])

Step 6 – Action – “Compose”

       Inputs : MaximumSales

That’s it. we got the maximum value. Stay tuned for the use case with this logic.

ByHariharan Rajendran

SAMEPERIODLASTYEAR in SSRS Report builder

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

SAMEPERIODLASTYEAR = CALCULATE(sum(DemoSales[Sales]),

SAMEPERIODLASTYEAR(DemoSales[SalesDate]))

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.

=Previous(sum(Fields!Sales.Value,”Day”),”Year”)

 

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

 

Warning:

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

 

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