Data visualization and analysis in today's fast-paced business environment require not only precision but also a deep understanding of the tools at our disposal. Power BI, a potent tool in Microsoft's suite, stands out for its ability to handle complex data scenarios. One such scenario is ranking data with multiple grouping dimensions, such as state, city, and revenue. This blog post will guide you through this process in Power BI. Understanding the Basics Before we dive into the specifics, let's ensure we have a clear understanding of some key Power BI concepts: Ranking: This involves sorting data in ascending or descending order. In Power BI, we can use DAX functions like RANKX to achieve this. Grouping: This refers to categorizing data into segments. Power BI allows grouping in visuals or more advanced grouping in DAX. DAX (Data Analysis Expressions): A library of functions and operators used in Power BI for creating custom calculations. The Scenario Imagine we have a dataset containing sales information across different states and cities over several years. Our objective is to rank these cities within each state based on their annual revenue. Step-by-Step Guide Prepare Your Data: Ensure your dataset is properly structured with columns for State, City, Year, and Revenue. Load Data into Power BI: Import your dataset into Power BI Desktop. Creating the Rank Measure: Go to the Data view. Create a new Measure to calculate the rank. You can do this by using the DAX formula: Building the Visual: Switch to the Report view. Create a table or matrix visual. Add State, City, Revenue, and the newly created Rank column to the visual. Conclusion Ranking and grouping in Power BI offer powerful ways to dissect and understand your data more effectively. By following the steps outlined in this blog, you can now rank cities within states based on revenue, offering valuable insights into regional sales performance. Remember, the key to mastering Power BI lies in practice and experimentation, so don’t hesitate to try different variations of rankings and groupings to suit your specific data needs.
Hello, here I am going to explain what are the ways to publish a Power BI report on the Power BI Service which is the Cloud solution provided by Power BI and on the on-premise solution, keep reading this blog and, you will be able to publish the report on the Power BI Service as well as in on-premise. Power BI Service(Cloud Solution) Prerequisites: Power BI Service Account:- You must have a Power BI Service Account in order to publish the report to the service. Power BI Desktop:- Install the Power BI desktop on your system. Note:- Before publishing the report, login in power bi desktop with your Power BI service account. There are simply three types: Publish from Power BI Desktop. Publish from Power BI Server. Publish using Power BI Rest API. 1: Publish from Power BI Desktop Publish from the Power BI Desktop is the easiest way to publish a Power BI report, First, open the report that you want to publish in Power BI Desktop then click on the publish icon. Figure 1: Power BI Desktop When you click on it, it will ask you to select the workspace that you want to publish on. Then click on the select button, Bingo!! You successfully published the report. Figure 2: Power BI Desktop 2: Publish from Power BI Service This is a second method to publish a Power BI Report, For that first log into the Power BI service and, open the workspace where you want to publish the report. After, Click on the Upload icon then browse your report, That’s it for the second method. Figure 3: Power BI Service 3: Publish Using Power BI Rest API Here, We are using a PowerShell script that will work based on Power BI Rest API to publish the Power BI report. It is really simple to use Power BI Rest API with PowerShell script to deploy the report. To use Power BI API in PowerShell Script we need to install the MicrosoftPowerBIMgmt module. Install-Module -Name MicrosoftPowerBIMgmt Here are the steps to deploy the Power BI report using PowerShell Script. Step 1: Create a required parameter Before publishing the report we need the following things that we will assign as parameter PBI Username:- Assign the Username of the Power BI service Account. PBI Password:- Assign Password of Power BI service Account. WorkspaceName:- Assign workspace name. Report Name:- Assign Report name. Power BI File path:- Provide full Power BI File path where is located Workspace object:- In This variable, we assign the workspace id from the workspace name Step 2: Make the password secure and create a credential variable. $SecurePassword= ConvertTo-SecureString -String $PBIPassword -AsPlainText -Force $Creds = New-Object Management.Automation.PSCredential -ArgumentList ($PBIUserName, $SecurePassword) Step 3: Connect to Power BI Service Connect-PowerBIServiceAccount -Credential $Creds Step 4: Publish the Report This is the final and very important step in this step we will publish the report. New-PowerBIReport -Path $pbixPath -Name $ReportName -Workspace $workspaceObject -ConflictAction CreateOrOverwrite On-Premise Solution Prerequisites: Power BI report server:- You must have a Power BI report server that is installed in your system with fully configured and, it must be set up with a Database, web URL, and web portal. Power BI desktop report server:- Install the Power BI desktop report server in your system. Note:- This is different than normal Power BI desktop There are simply two types: Using Power BI Desktop. Using Power BI Report Server. 1: Using Power BI Desktop. Open the report that you want to deploy then Open the “File” navigation option(situated in the upper-left corner) and, click on “save as”. Figure 4- Power BI Desktop File panel Note:- When you click on “Power BI Report Server” you will get the below message where you have to give the web portal URL URL from the step of configuring the report server. Figure 5:- Adding Report Server Address(Source:- Radacad) After successful deployment, you will see a message with a link to the report. Figure 6:- Successfully published report(Source: Radacad). 2: Using Power BI Report Server. This is a different way to deploy the report so, first you have to open Power BI Report Server. Figure 7:- Power BI Report Server(Source: Radacad) Click on “Upload” as mentioned in the above picture and upload Power BI Report from your files Conclusion I Hope I’ve Shown You How Easy It Is To deploy Power BI reports on the Report server as well as on-premise, So far we have learned the step-by-step process of it.
While working on an Embedded Power BI report, I got a requirement to implement Row Level Security. E.g. Region heads can see data of their Region only. This report is accessible from the .Net MVC application. Locations are assigned to users from the application. This report is hosted on Power BI Server and authenticated by a .Net Application with predefined credentials. Sample Data: There are two tables: Finance Users Figure 1:- finance Table Figure 2:- User Multiple Options to achieve the same with Power BI We have found out following options to implement the same: RLS (Row Level Security) Through Query String Control report filters that will use in embedded code There are certain limitations with Option#1 and Option#2 (I will describe them later), so moving on with Option#3 “Control report filters” Implementation with “Control report filters that will use in embedded code” When you embed a Power BI report, you can apply filters automatically during the loading phase, or you can change filters dynamically after the report is loaded. For example, you can create your own custom filter pane and automatically apply those filters to reports to show user-specific insights. You can also create a button that allows users to apply filters to the embedded report. Step 1: First Identify the filter based on your requirement There are five types of filters available Basic - IBasicFilter Advanced - IAdvancedFilter Top N - ITopNFilter Relative date - IRelativeDateFilter Relative time - IRelativeTimeFilter Based on my requirement I have used a Basic filter and you can use others based on your need. If wants to know more about others click here Here in this blog I am continuing with the #1 basic filter method Step 2: Determine the table and column that you wants to filter In my case, I want to filter the table ‘finance’ and my column was ‘country’ because when particular user login the country column must be filtered For example:- user G1 login the country = ‘Germany’ and this column in table ‘finance’ Table:- ‘finance’ AND Column:- ‘country’ Step 3: Put this two things in this code Step 4: Make Dynamic You can see into this code that there is ‘values’ where I pass “USA” and “Canada” that is hardcode but we want that dynamic values that have to be changed based on which user is login That’s why I made variable that contain ‘country’ name which is assigned to particular login user, For example:- the variable name is ‘Array’ If user G1 login this variable return the value [“Germany”] and set this variable to value Like: Note: if login person has multiple locations than that variable should return values like [“Germany”, ”USA”, ”India”] Note: if login person is manger or CEO they can see all the country data for that Variable has to return Null instead of blank array [ ] Step 5: Put This code into embedded code. Step 5.1: Identify your Method User-owns-data( Embed For your Organization) App-owns-data (Embed for your customer) Step 5.2: According to your method put this code in the given place For the #1 method Add this code into [EmbedReport.aspx] file Figure 3: User-owns-data For the #2 method Add this code into [EmbedeReport.cshtml] file Figure 4: App-owns-data Figure 5: Sample And that’s it. Now, try to run the report from the application and it should work as expected. Feel free to reach out to me, if you face any issues. Conclusion I Hope I’ve Shown You How Easy It Is To Implement Row Level Security, So far we have learned step by step process of it. We started with identifying the filter and then finding out the actual table and column that we want to filter on and generating the code. last but not least put this code into our embedded code.
