ByHariharan Rajendran

Report Share + Viewer Access in Power BI

This blog post explains how Power Bi Admin can allow users with viewer access share their reports with others.

Power BI has 4 different levels of access with different options enabled and disabled.

  1. Admin
  2. Member
  3. Contributor
  4. Viewer

When workspace admin assign a viewer access to user then user can see only the reports and features with in the report. User can’t share the report with other as the viewer access doesn’t allow user to see the share report option.

If user wants to get the share option on top of the viewer access then admin can enable the share option to the user. Follow the steps to enable the share option.

  1. Choose the report which you want to allow user to share with others
  2. Click the share option and enter the user email address who already added as a viewer in the workspace.
  3. If user wants to share all the reports then admin should repeat the above step for all the reports.

Check the below video to know details steps and what are all possible with viewer access with build permission.

ByHariharan Rajendran

[Solve] – Fix the column names in Excel export from Power BI Desktop Report

This blog post explains you that how to fix the column name mismatch in the Power BI visual export. This is not a major issue for most of us but sometimes user might complain about the column name mismatch when they directly compare the UI result with excel export.

How to reproduce it?

This issue occurs in Power BI Desktop report. This issue will occur when you use the direct measure (implicit measure) in the visual.


I have table called Dummy where I have 2 direct columns – Department & Sales. I am just creating a simple and visual and exporting the visual data into csv using export data option in Power Bi Desktop itself.


The result of csv file will have proper column names.

Try publishing the same report to service and export there into excel and we will get the below column names.

The name of Sales column is changed into “Sum of Sales”. The default summarization of this column is SUM so the name of column has changed.

Note- The names will be same if we export into csv both in Desktop and Service.


The solution is very simple. Create an explicit measure using DAX and use that measure in your visual.

Created a new DAX measure called SalesValue and used that measure in visual. If we try to export into excel then we will have proper name as like below.

Happy Learning. Comment if you have any other approach.

ByHariharan Rajendran

Paginated Reports from Power BI Service and Report Server (On-Prem)

This article explains that on-prem report server is the best option for paginated reports as of now as per my experience and I am also recommending you to use the hybrid approach wherever it is possible. You can maintain a hybrid structure like, Power BI Desktop reports in Power BI Service and Paginated reports in On-prem server. This is something that you can prefer where you will get the more stable version of product with more features.

I am sure, Power BI team will make the Power BI Paginated Report as a stable version with tons of feature in future.

I am highlighting few points in which Report Server is getting more score than the Power BI Service.

License – if your organization has Power BI Premium Capacity node then you can get the report server product key and use them for on-prem installation or you can get it if you have SQL Server Enterprise Edition with Software Assurance.

Report Render Time-Out issue  – We often encounter this issue in one of my client. We are using SQL Server and the database and we are handling large tables. If we run the query, it will take at least 5 to 6 mins due to relationship with multiple tables and conditions.

We have done all level of performance optimization but still same performance. If we use the same script in Report Builder it is taking more than 10 mins and same in service taking more than 10 mins and after that it end up with an error. When we check with Microsoft support engineer, they were highlighted that the issue is because of Authentication token expiration but not sure and there is no fix as of now. We tried all the approaches to overcome the issue but finally we end up with Report Server as an alternate solution for these kind of time-out issues. In Report Server we do have control to setup the time-out settings.

Subscription – Our customer had requirement to get the report’s excel export into their email or network drive. Power BI Service has Email option but no network drive as an out of the box feature. We need to use Power Automate to achieve the result which will be difficult for business users. This network file share is one of the default option in on-prem Report Server. Also we do have data driven subscription.

Gateway  – Gateway is one of the component in Power BI Service which helps to connect Power BI reports with on-prem data sources. We always need to map the report with gateway data sources. If you have Power Bi Desktop reports then you can map the dataset once with Gateway datasource and you can replace them every time whenever you do the changes on the report and you no need to map the data source again and again for the same report where as you need to map with data source again and again whenever you overwrite the paginated report (rdl).

Security – In terms of security, we have to leverage only the pre-defined access levels which are Admin, Member, Contributor & Viewer in Power BI Service. These pre-defined access levels are more than enough for most of the cases but on few cases we may need to limit certain access. We have same kind of access levels in report server but we do have control to create our own access with defined controls. It will give more control to manage the access levels.

If you have PII data and you dont want to move the data outside of your network then report server is one of the best option.

Data Source Support – This is one of the feature that I highlight Power BI Report server is best. Power BI Paginated report support only limited data sources where as we have few more additional data sources like ODBC, OLEDB and etc supported in Report server paginated report.  ODBC and OLEDB can cover many data source connections possible with Report Server report. One of the highlight of Power BI Paginated report, it supports Power BI datasets as a source which is not supported in report server.

Management – Power BI Service has workspace concept to maintain the reports for different teams and groups and etc. If you have multiple sections under one main group then we need to maintain multiple workspaces. In Report Server, all are folders, we can create folders, sub folders and can control the access to root folder or sub folder or report.

Additional Features – Power BI Report Server supports below features which is not fully available in Power BI Paginated reports yet.

1. History Snapshot

2. Cache control

3. Central Data Sources and Datasets

4. KPIs

5. Document Map

6. Drill-through reports

7. Linked Reports

Development Tools – Power BI Report Builder is 32 bit and light weight tool and we have same kind of report builder for report server. Both of the tools hanging when we handle report with huge number of rows as a result. If you choose report server then you can use SQL Server Data Tools with Visual Studio and you can create reports and deploy. The development experience will be good when you choose Visual Studio.

Share your thoughts here.

ByHariharan Rajendran

Will Power BI recognize Defined Names in Excel?

Most of you familiar with accessing excel files from Power BI Desktop. Let me as a below question.

Will Power BI recognize “Defined Names” from Excel?

If you take excel expert who use most of the features from excel for analysis, they may create multiple sheets, tables (range of cells) & defined names (range of cells). They may be interested to see all the objects in Power BI. Will Power BI help excel experts to access all the objects?

The above two questions are same but slightly different in context. Yes, is the answer for above two questions.

Let me explain Define Name in Excel first.

Excel – Define Name

Like table, we can define a name for range of cells. It is kind of subset of data from the whole sheet data.

You can find the “Define Name” under Formulas tab.

Select the Define Name, it will ask you to enter the name and range of cells in refer section as like below.

I have defined a name called “Profit” with L column. This will be smaller table with Profit as a column within the table.

You can define multiple names, and all can be referred in your excel calculations. You can check the list of names you defined as like below.

Now, you got a basic idea of Define Name in excel. Refer excel articles to understand more about this feature.

Let us come to our Question again. I said Yes, Power BI will recognize these names. Let me show you.

Power BI

If I connect this excel in Power BI Desktop, you can below objects under Navigation.

  1. Sheets
  2. Tables
  3. Defined Name – as table

In the above screenshot, Country, Sale_Price & Sales are defined names.

All the above defined names are recognized as tables in Power BI. We can use them for report.


You may get the following question; will it be any performance issue when we use these defined names?

It depends on the scenario. Consider you have a excel with more number of columns and you have defined a name with few columns and measures. In this case, you can consider only the defined name and no need to use the full sheet in Power BI. This will improve the performance.

Try to avoid using the whole sheet and defined names together because defined name is already available in whole sheet and Power BI refresh will take time to refresh as it needs to refresh the whole sheet and defined name.

Happy Learning!!

ByHariharan Rajendran

Power BI Export Report into PDF – REST API Support

This article explains a most wanted solution in most of the organizations. Yes, it is talking about exporting a Power BI report into pdf, ppt and png. You may think that these options are already available and why I have taken this topic. Yes, we can export a report into pdf or ppt but we manually need to go to report and choose the export option to get the report in either pdf or ppt.

I will tell you how to automate this process, I mean you no need to open the report manually and export it. We also have subscription option which does the same but not in an effective way. I would say still (when am writing this article). Power BI does not have a proper subscription feature. There are lot of limitations. Check Power BI Subscription in Microsoft documentation.

This article refers the Power BI REST API which release by Power BI team recently for exporting the report into pdf, ppt and png. Refer –

I would say this is fantastic update from Power BI team which enables us to perform so many options and it will be the alternative for proper subscription feature in Power BI.

Use Cases

  1. Build your own custom subscriptions
  2. Enable users to download their report into pdf whenever they need without opening the report.
What are those REST APIs?

We have 2 to 3 REST APIs to support following actions.

Export the file –{groupId}/reports/{reportId}/ExportTo

Check the Status –{groupId}/reports/{reportId}/exports/{exportId}

Download the file –{groupId}/reports/{reportId}/exports/{exportId}/file

The above all REST APIs are support in both My Workspace and App workspace but the workspace should reside in Power BI Premium Capacity.

The Following article explains how to use these REST APIs in C# code –

Let me explain, how to use PowerShell script to perform export the file and download the file.

What you need to test?
  1. Register an Azure APP and get the Client ID –
  2. Power BI Premium Capacity to your workspace – You can use your Power BI Premium Capacity or Azure Power BI Embedded Capacity.
  3. Sample report published into your workspace
  4. Get Workspace & Report ID

Open your Power BI report in browser and get the id.

In the above image, id after groups is Workspace ID or GroupID and Report ID is available after Reports.

Export a File

Use the below URL,$Group_ID/reports/$Report_ID/ExportTo

Replace the $Group_ID and $Report_ID with whatever you got from your report URL.

Export file REST API is a POST method and it also requires Body when we request the URL. I have using pdf format.

Refer the below PowerShell script.

$uri = “$Group_ID/reports/$Report_ID/ExportTo”

$body = “{`”format`”:`”pdf`”}”

$FileExport = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method POST -body $body

 Get File Status

Above step triggers the export file job, we need to know the status of the export. If the export status is completed means the file is ready to download.

This is a GET method.

Get the Export file id from above PowerShell script and use it in below REST API.

Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Use the below PowerShell script.

$id = $

$uri = “$Group_ID/reports/$Report_ID/Exports/$id”

$FileStatus = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Get or Download a file

Again it is using GET method. Following URL is the REST API URL.$Group_ID/reports/$Report_ID/Exports/$id/file

Here $id is Export file id which we get from Export file section.

Use the below PowerShell script.

$uri = “$Group_ID/reports/$Report_ID/Exports/$id/file”

$File = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET -OutFile $Folder+”_new.pdf”

I am using -OutFile option to get the file stored in local folder with filename with extension as .pdf.

Get the full script from my GitHub Repository.

Next article, I will cover the Use cases with this REST API.

ByHariharan Rajendran

DAX – Daily vs Weekly vs Monthly Measures

This blog post explains, how to use Daily, Weekly & Monthly measures in single chart or table in Power BI. This might be familiar to you, but I keep getting this question about this.

If you want to build a chart only daily or weekly measures then it pretty easy as you can use the date dimension column and take a measure and it will show the daily result when you have proper relationship between the fact and dim – date tables.

When we want to compare daily vs weekly vs monthly, then we end up with using all the measures in single date context. We need to play with CALCULATE to overwrite the filter context for weekly and monthly measures.

First, lets see how to build individual measures. As I said, it is very simple.

I am taking below table for example.

Daily Count – Simply take date and count column and create a chart or table in Power BI.

Weekly Count – Simply take Week and count column and create a chart or table in Power BI.

Monthly Count – Simply take monthly and count column and create a chart or table in Power BI.

Now, let us try to merge them all together to achieve like below.

Let me explain to create the measures,
1. Daily Count
2. Weekly Count
3. Monthly Count.
I have created following columns – Year Week & Year Month as we need to create logic to support even for multiple years.
Year Week = Year & Week — 202014
Year Month = Year & Month — 20203

Please use the proper logic to get the correct digits. I left as simple just for demo. In a real-time, you need to handle month single and two digits. Same for week.

Daily Count – No need to build any measure, we can just directly use Date and Count column.
Weekly Count – As we are keeping date column, we need to override the context to get the sum of daily count for that specific week.

Weekly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Week] = Max(TD[Year Week])))
TD – is my table name
I have used Year week column.

Monthly Count – this is same as weekly count. Use Year Month column.
Monthly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Month] = Max(TD[Year Month])))

Finally, we can combine all the above measures into single visual.

Hope you like this approach. Please share if you find any other alternate options.

ByHariharan Rajendran

Power BI Report Subscription as PDF

This post explains you how to get the Power BI report subscription as a PDF format. Yes, this will be an interesting solution because Power BI doesn’t send subscription report in pdf/pptx formats. It is always image file as of March 2020. We may expect pdf/pptx formats in future updates.

There are many paid 3rd party applications are available to get the report in pdf formats but this post explains you the process or automation which converts the report easily with using your Power Automate license. Most of the organization are using O365/M365 for Power BI subscription. Those are still getting Power Automate licenses are free. Please check with your M365 admins.

Let me explain the scenario,

I have a report called “IT Spend Analysis Sample” in my workspace and I set up the subscription for all the pages of the report. As you know, it will trigger an email for each report page. I need to get a pdf with all the reports pages (images).


As we get separate emails for each report page, we need to collect all the images and combined them into a single file and get as pdf format.

I am using Power Automate for this whole process. This solution is using SharePoint folder to place the images and final pdf file but this is also possible with OneDrive & Local Folder or any other storage that Power Automate supports.


  1. Get all the image files from Email and place it on the SharePoint folder.
  2. Image to html conversion and place the html file on SharePoint folder.
  3. You can open and print the html file and save it as pdf file – This is a manual process which you need to perform as we have some issue with the size of the image on the pdf.


Step by Step

  1. I have created one Power Automate flow which extracts the image from outlook and place it on the SharePoint folder. There is a Power Automate template available for this process already so we can use this easily.


  1. Next, the main process – convert the images into single html file. Follow the below steps in Power Automate.
    1. Start with Recurrence Trigger or manual trigger based on your requirement. Set the time when you need to get the file place on the SharePoint. If you set 7 pm as Subscription time on Power BI report then try to set 7.15 pm on the Power Automate flow. because the other file should get the image from mails and place it on the SharePoint folder.
    2. Use “Get files (properties only)” action and point to the Power BI report images as like below.
    3. Initialize 3 variables – FilePath, JPG & Report.

            4. Next, “Apply to each” condition.

5. Set variable FilePath as “Filename with Extension”.

6. Next, “Get file content using path” action. Check the value below.

7. Set variable JPG as – dataUri(body(‘FileContent’))

8. Next, Append to variable Report – concat(‘<img src=”‘,variables(‘JPG’),'”/>’)

9. All the above processes are within apply to each loop and next add an action after apply to each. Create file in              SharePoint with html as file extension. Use variable report as file content.

That’s it. Now, run the flow it will store the pdf file on the given folder as like below.

Open the html and check the result. It will have all the images as one by one which is nothing but the report pages.

You can print the html and save as pdf. It is a manual process.

Added the screenshot of full flow. Please check it and let me know if you have any doubts.

One more information, Microsoft Power BI team has released new REST API to easy this process. I will cover that in my next post so stay tuned.

Happy Learning!!!


ByHariharan Rajendran

Power BI Report Server – Change Data Source using REST APIs

This post gives an idea that you can change the Power BI report’s data source without using Power BI desktop. Do you think is it possible? I am talking about Power BI Report Server.

Yes. This is possible with the help of Power BI Report Server REST APIs. You can go through the supported API methods using the below URL.

The syntax of the REST API is http://<reportservername>/reports/api/v2.0/


I have a report published into Power BI Report Server portal and I want to change the database used on the report to some other database. In this example, I am using a database called IT_Dev on my report and I will be changing the database to IT_Prod. Even you can change the server name also.

I am using Postman tool to access the REST APIs. Please check my below article to know about using the Postman with Power BI Report Server REST APIs.

Power BI Report Server REST API with Postman

We need to perform 3 main steps.

  1. Get the report ID of which we want to change the data source
  2. Get the Current data source information of the report
  3. Update the data source

Initially the below data source is used on the report.

Get Report ID

Use the below REST API to get the ID of the report. It will show all other information of the report. Since we are using GET method, you can access this URL on the browser itself.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports

Here PowerBIReports is a REST API which will list out all the reports from report server.

Note down the report ID of the report.

Get Current Data Source Information

As we are trying to update the data source, we need to know the structure of the current data source so that we can use the same structure while updating the data source.

Method – GET

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We need to use the captured ID on the above URL.

Note down the Value section of the result.

Update the Data Source

In this step, we will update the database from IT_Dev into IT_Prod.

Method – PATCH

URL – http://localhost/reports/api/v2.0/PowerBIReports(6b20ecf7-54b6-40da-8970-92a8576f665a)/DataSources

We are using the same URL but with different methods. And, we need to paste the data source value script which we copied on the previous step on the body section. Change the database name and enter the username and password.




“Id”: “2dc59c3d-e941-ea11-bed7-08edb9949ce0”,

“Name”: null,

“Description”: null,

“Path”: null,

“Type”: “DataSource”,

“Hidden”: false,

“Size”: 0,

“ModifiedBy”: “<domain\username>”,

“ModifiedDate”: “2020-01-31T19:39:14.067+05:30”,

“CreatedBy”: “<domain\username>”,

“CreatedDate”: “2020-01-28T21:44:21.507+05:30”,

“ParentFolderId”: null,

“IsFavorite”: false,

“Roles”: [],

“ContentType”: null,

“Content”: “”,

“IsEnabled”: true,

“ConnectionString”: “data source=.;initial catalog=IT_Dev;persist security info=False”,

“DataSourceType”: null,

“IsOriginalConnectionStringExpressionBased”: false,

“IsConnectionStringOverridden”: false,

“CredentialRetrieval”: “prompt”,

“CredentialsByUser”: null,

“CredentialsInServer”: null,

“IsReference”: false,

“DataSourceSubType”: “DataModel”,

“DataModelDataSource”: {

“Type”: “DirectQuery”,

“Kind”: “SQL”,

“AuthType”: “Integrated”,

“SupportedAuthTypes”: [





“Username”: “<yourusername>”,

“Secret”: “<YourPassword>”,

“ModelConnectionName”: “d0f1b0ce-c7e4-4ff9-b72f-3df06f48f6fe”




That’s it. If you go and check the report data source then you can see IT_Prod.

Video explanation

Please leave your comments below. Happy Learning!!