Understanding Import and DirectQuery Modes in Power BI Power BI empowers users to analyze data from various sources. This post dives into two key connection modes: Import and DirectQuery. Each offers distinct advantages depending on your data analysis needs.   Import Mode: Power and Flexibility Import mode brings your data directly into Power BI's internal memory. This creates a copy of the data, allowing for: Faster Performance: Since the information is readily available, visualizations and calculations happen swiftly. Enhanced Data Manipulation: Transform and mold the data to your liking before loading, offering greater control compared to DirectQuery. Offline Accessibility: Reports built with import mode function flawlessly even without an internet connection. However, there are limitations to consider: Resource Demands: Importing large datasets strains system resources like RAM and disk space. Data Refresh: Changes made to the source data won't be reflected until you refresh the import. Setting up automatic refreshes can help, but large datasets can lead to slow refresh times.   DirectQuery Mode: Real-Time Insights DirectQuery mode bypasses internal storage. Instead, it sends queries directly to the original data source. This approach offers several benefits: Real-Time Analysis: Always see the latest data without manual refreshes. Changes in the source database are instantly reflected in your reports. Large Dataset Efficiency: DirectQuery handles massive datasets effectively, avoiding memory constraints encountered in Import mode. Guaranteed Accuracy: Reports always showcase the most up-to-date information. However, DirectQuery comes with its own limitations: Limited Functionality: Certain features like calculated columns and complex data models are restricted due to the reliance on live data. Potential Performance Lag: Queries travel back and forth between Power BI and the source system, impacting response times compared to Import mode.   Let’s take a look on how Import & Direct Query modes works One of the main advantages of using Power BI is its ability to import data from various online sources. To import data from your database directly into your Power BI reports and dashboards, you need to connect to the database first. Here are the steps to follow:  Open Power BI and click on the “Get Data” button. In the “Get Data” window, select the “Database” option. Choose SQL Server option. Enter the server name and credentials to connect to the database. Select the specific tables or views you want to import data from. Click on the “Load” button to import the data into Power BI or you can Transform the data if any necessary transformations or filters you wants to apply.   Setting Up a DirectQuery Connection Except for the import mode prompt, the steps for configuring a DirectQuery connection will remain the same. Choose the DirectQuery option when prompted for the import mode. Choosing the Right Mode The optimal mode hinges on your specific needs: Import Mode: When speed, offline access, and intricate data manipulation are paramount, Import mode shines. It fosters a responsive environment for in-depth analysis, ideal for creating reports and dashboards that can be explored without an internet connection. This mode is particularly advantageous for smaller to medium-sized datasets, where refresh times are manageable. DirectQuery Mode: This mode is your go-to for real-time insights. It ensures you're always basing your decisions on the freshest data available, minimizing the risk of outdated information influencing critical choices. For very large datasets, DirectQuery eliminates the memory limitations of Import mode, making it a powerful tool for handling massive volumes of information. By understanding the strengths and weaknesses of each mode, you can leverage Power BI effectively to make informed decisions based on your unique data analysis requirements.

Introduction: Welcome to our guide on normalizing JSON files using Power Query! JSON (JavaScript Object Notation) has become one of the most popular formats for storing and exchanging data due to its simplicity and flexibility. However, working with JSON data in its raw form can sometimes be challenging, especially when dealing with nested structures or arrays. In this blog post, we'll delve into the process of normalizing JSON files using Power Query. Normalization refers to the process of organizing data into a tabular format, making it easier to analyze and manipulate.    First we need to import data in power bi desktop with Json connection.  click on get data inside home tab of power BI Desktop.   Click on more tab to get Json file connection. click on connect button which redirect you to your systeam then choose your Json file.   You redirect to your Power Query Editor. Power Query editor interface like below. Here we can check Table.column ,Table.Rows are in form of list Not in form of row data that we want. Click on one of list which show what inside of list. Here data inside of one  list.   It has around 121 rows inside one list and we need to expand it Row wise. so first idea is to exoand list column by left click on right side of column and choose expand new rows for every column which contains list.   But you notice that for one row there are multiple sub-rows which is not appropriate like below. Here for extract time(First row) only one row valid other rows are not valid or duplicate so these happen with every rows and roes count for table is not acceptable. so our approach is not valid here we miss something In above image(image 5) table.rows and table.column has list in row. if you turn by turn expand both list you notice that both list has data(in some case same rows and in other case rows are not same) with same row count which indicate that both lists data are connected with each other. so we need to make a new column which combine both list in form of table . Go to add column tab> select custome column  it open  custome column interface and write code like below. Table.FromColumns({[Tables.Columns],[Tables.Rows]})  it add new column in existing table like below. when you click on customecolumns row you will redirect to expanded version of table which look like below.   now we can normalize data. now we can separate table to multiple table by for easy for data modeling  create duplicate table and filter out as needed  now close your power query tab and you get your desired output below is advance editor code if you don't want to apply all  steps that we implemented above let Source = Json.Document(File.Contents("C:\Users\MagnusMinds\Downloads\Order_CRHESTHASH_ET_FD2024-03-11_PID184_ORD686_MRK2024-03-08 09 14 44.410_T00638455040948575166.json")), #"Converted to Table" = Table.FromRecords({Source}), #"Expanded Tables" = Table.ExpandListColumn(#"Converted to Table", "Tables"), #"Expanded Tables1" = Table.ExpandRecordColumn(#"Expanded Tables", "Tables", {"Name", "QueryStartTimeUtc", "QueryEndTimeUtc", "QueryElapsedTimeMs", "CurrentRow", "Columns", "Rows", "OriginalRowCount", "Hashes", "MasterRecordIsDuplicate"}, {"Tables.Name", "Tables.QueryStartTimeUtc", "Tables.QueryEndTimeUtc", "Tables.QueryElapsedTimeMs", "Tables.CurrentRow", "Tables.Columns", "Tables.Rows", "Tables.OriginalRowCount", "Tables.Hashes", "Tables.MasterRecordIsDuplicate"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Tables1",{{"Tables.Name", type text}, {"Tables.QueryStartTimeUtc", type datetime}, {"Tables.QueryEndTimeUtc", type datetime}, {"Tables.QueryElapsedTimeMs", Int64.Type}, {"Tables.CurrentRow", Int64.Type}, {"Tables.Columns", type any}, {"Tables.Rows", type any}, {"Tables.OriginalRowCount", Int64.Type}, {"Tables.Hashes", type any}, {"Tables.MasterRecordIsDuplicate", type logical}}), #"Expanded Tables.Rows" = Table.ExpandListColumn(#"Changed Type", "Tables.Rows"), #"Added Custom" = Table.AddColumn(#"Expanded Tables.Rows", "Customcolumn", each Table.FromColumns({[Tables.Columns],[Tables.Rows]})), #"Expanded Customcolumn" = Table.ExpandTableColumn(#"Added Custom", "Customcolumn", {"Column1", "Column2"}, {"Customcolumn.Column1", "Customcolumn.Column2"}), #"Expanded Customcolumn.Column1" = Table.ExpandRecordColumn(#"Expanded Customcolumn", "Customcolumn.Column1", {"Name"}, {"Customcolumn.Column1.Name"}) in #"Expanded Customcolumn.Column1"  

Introduction Have you ever tried to add holidays which lie between today (or any dates) to a specific date or the same kind of scenario? If yeah Then this article helps you   Scenario Recently, I faced a situation where I needed to add days to the current date (today) which depends on the total number of hours left for a particular employee. Means employee A has 150 hours left and spent only 6-hour for one working day, then divide the hour by daily spend and find the date (forecast date) where employee A completed their task but did not include holidays and weekends. Let’s consider employee X. Get 15 fore-cast days on the basis of the hours left. Those 15 days do not include weekends and holidays, which we need to add to the current date (today). Let’s consider Employee X got 3 days of weekend or public holiday out of 15 days, then it also needs to add in fore-cast days, which means now 15 days become 18 days.  If employee X's forecast date falls on a weekend or a holiday, it is also not applicable.   We take some data for solving scenario   Holiday Table   Logic behind solving this problem is we going to take a separate date table which will help for deriving fore-cast date and add new column of Holidays. After that we add a new column which give us a flag of 0 if Date in weekends or in holidays and 1 for date in working day after that we give rank according to flag with exclude of Flag 0.   We make a new date Table which particular Use for Fore-cast Date Using Below DAX Code Here I am taking only 2024 Date field fore-cast Datetable = ADDCOLUMNS( CALENDAR( TODAY(), DATE(2024,12,31) ), "weekday", WEEKDAY([Date],2), "Dayname", FORMAT([Date],"dddd") )   Join holiday Table and fore-cast Date table using Date field     Make a new column (Holiday) in Fore-cast Date table which help us to bring only holiday date from holiday table to existing table holiday = RELATED('Holiday Table'[holiday date])   Make another new column with some condition using Dax code which give Flag 0 for those date which are weekends and holidays and 1 for Working days. workingday = IF( NOT('fore-cast Datetable'[weekday]) in {6,7}, IF( 'fore-cast Datetable'[holiday]<>BLANK(), 0, 1 ), 0 ) Add column Which Give day no from Today with ignoring Weekends and holidays Day Number = RANKX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[workingday]), 'fore-cast Datetable'[workingday]<>0 ), 'fore-cast Datetable'[Date],,ASC )   Our main goal is taking Days on behalf of Hours and find those date which is equivalent to left days from today with ignoring weekends and holidays. Day Number column give us that ability   Fore-cast date table Look like below Now make a new column in employee table Forecast date column = var a =FLOOR((Employee[Hour left]/6),1) return MAXX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[Day Number]), 'fore-cast Datetable'[Day Number]=a ), 'fore-cast Datetable'[Date] )   We can make measure with little bit changes fore-cast date using measure = var a = FLOOR(SUM(employee[hour left])/6,1) return MAXX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[Day Number]), 'fore-cast Datetable'[Day Number]=a ), 'fore-cast Datetable'[Date] )   Make a table visual     If you don’t want to add new table to your Existing data model We can also achieve Without fore-cast date table by creating it on fly.   Use below Dax code for column fore-cast date without table refrence = var a = FLOOR(('Employee'[Hour left]/6),1) var b =NETWORKDAYS(TODAY(),TODAY()+a,1) var c = ADDCOLUMNS( CALENDAR(TODAY(),TODAY()+a+b), "weekday", WEEKDAY([Date],2), "dayname",FORMAT([Date],"dddd"), "publikholiday",LOOKUPVALUE('Holiday Table'[Holiday Date],[holiday date],[Date]), "holidaycondition", var a1 = IF( not(WEEKDAY([Date],2)) in {6,7}, IF( LOOKUPVALUE('Holiday Table'[Holiday Date],[holiday date],[Date])<>BLANK(), 0, 1 ), 0 ) RETURN a1 ) var f = ADDCOLUMNS(c,"rank",RANKX(FILTER(c,[holidaycondition]<>0),[Date],,ASC)) var e= MINX(FILTER(f,[rank]=a),[Date]) return e here little bit changes in above code for measure Use below code for measure fore-cast date measurewithout table refrence = var a = FLOOR(sum(employee[hour left])/6,1) var b =NETWORKDAYS(TODAY(),TODAY()+a,1) var c =ADDCOLUMNS( CALENDAR(TODAY(),TODAY()+a+b), "weekday",WEEKDAY([Date],2), "dayname",FORMAT([Date],"dddd"), "publikholiday",LOOKUPVALUE('Holiday Table'[Holiday Date],'Holiday Table'[holiday date],[Date]), "holidaycondition", var a1 = IF( not(WEEKDAY([Date],2)) in {6,7}, IF( LOOKUPVALUE('Holiday Table'[Holiday Date],'Holiday Table'[holiday date],[Date])<>BLANK(), 0, 1 ), 0 ) RETURN a1 ) var f = ADDCOLUMNS(c,"rank",RANKX(FILTER(c,[holidaycondition]<>0),[Date],,ASC)) var e= MINX(FILTER(f,[rank]=a),[Date]) return e   Output without Using Table refrence

Introduction to Power BI and Year-to-Date(YTD) & Year-over-Year(YoY) Calculations Power BI is a data visualization and business intelligence tool that allows users to connect to different data sources, transform data, and create insightful reports and dashboards. With Power BI, users can easily perform complex calculations such as YTD calculation, which provides a way to view data from the beginning of the year up to a given point in time. YoY growth is a change in a metric compared to the same period one year prior. There are several approaches to achieve YTD & YoY calculation using DAX in Power BI. Let's use one of the approach to accomplish that.   What is Year-to-Date(YTD)? Imagine you’re in February, looking back at all the data from the beginning of the year (January 1st) until today. That’s YTD. It’s like a running total of your performance throughout the current year.   How to Calculate Year-toDate(YTD)? Assume we have a calendar & sales table and having a column for sales amount. Now use DAX to develop a measure that computes the current year's YTD revenue. Previous Year-to-Date(PYTD): Now, rewind to the same day in the previous year. The data from January 1st of that year up to that day is PYTD. It’s your benchmark, a reference point to compare your current year’s progress.   How to Calculate Previous Year-to-Date(PYTD)? Using SAMEPERIODLASTYEAR function we can get the same date of previous year. Year-over-Year(YoY) Growth: This is where things get exciting! YoY is the change between your current YTD and the PYTD for the same day. It tells you how much you’ve grown (or shrunk) compared to the same period last year.   How to calculate YoY growth : Subtract PYTD(YTD Rev LY) from YTD Revenue(YTD Rev) :   The DAX functions I utilized to get these calculations : LASTDATE(Dates) : Returns last non blank date STARTOFYEAR(Dates) : Returns the start of year SAMEPERIODLASTYEAR(Dates) : Returns a set of dates in the current selection from the previous year CALCULATE (Expression,Filter,Filter, …) : Evaluates an expression in a context modified by filters. DATESBETWEEN(Dates,StartDate,EndDate) : Returns the dates between two given dates.   Conclusion : Calculating YTD and YOY growth in Power BI using DAX is a valuable technique for analyzing financial performance and identifying trends. Furthermore, it's important to note that this comprehensive approach leverages only pre-defined DAX functions. By understanding and practicing these versatile functions, you can unlock the ability to perform a wide range of complex calculations within Power BI, ultimately transforming your data into actionable insights.

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.