In this blog we will learn how to hide the filter panel in Microsoft Power BI report. Once you publish the Power BI report in Power BI service, filter panel is useless for the end user. In order to hide the filter pane, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on filter pane. Step 3: There is a eyeball icon at the top of the filter pane that can be toggled. Save the report and publish to your workspace. Before published the report looks like below. After the published the report, user can not see the filter pane. You can also do this in the Edit Report in the service. Hope this helps!

Dashboard is created to get a brief overview of your report by pinning visuals to an Existing dashboard or to a new dashboard. But sometimes it might be required to pin all the visuals of your page in the report to your dashboard. This blog will guide you through how this can be achieved.

Instead of viewing dashboard and report on Power BI Web Service, we can directly view it in CRM. We need to publish the Power BI dashboard to our CRM Environment. This Blog will guide you through it can be done.

In this blog we will learn how to append two or more table into new table or existing table. The Append Queries in Microsoft Power BI is an equivalent of UNION ALL in SQL. Consider two sample customer table; one for CustomerOne: And Customertwo: Open Microsoft Power BI for Desktop > Get Data > Excel > the excel file. You should see this: Select tables and click on Transform Data. Now it’s time to proceed with the Append operation itself: Click the little triangle on the main “Append Queries” button. You’ll get 2 options: Append Queries – this operation would add rows into an existing table Append Queries as New – this operation will create a new output table from 2 (or more) appended tables. I’m going for this option. So, let’s see what happens after clicking the “Append Queries as New” button: You either append 2 tables like me OR you can do “Three or more”. So, keep in mind you are NOT limited to 2 tables only. One important thing to understand how the tables are actually “appended” together. Power BI looks at column names. If it finds the same columns like in my case (CustomerID, First Name, Last Name, Contact No) in both the tables, it won’t create any new columns and it will fit everything right into those 4 columns. What about Duplicates? Append Queries will NOT remove duplicates. You have to use Group by or Remove Duplicate Rows to get rid of duplicates. So, you press OK and you get the result of your operation. Now the result: Hope this helps!

This Blog will show you how you can Install and locate your Plugins in XRM new interface.

The following steps are to be followed to register an application entity in Azure Active Directory:- Go to Azure Active Directory and Click on App Registrations. Click on New Registration, Enter a name and the Redirect URL and click Register. Copy and save the Application(client) ID and Tenant ID in a text file. Generate a key for the Client ID from Certificate & secrets section.Go to Certificate & secrets and click + New Client Secret.

Introduction: This blog will show you how you can color individual rows differently based on different conditions and the row headers in matrix (Not alternating rows). Our Scenario: I want to apply colors to different rows of the following Buckets: Current – No Color 1-30 Days Past Due – Yellow 31-60 Days Past Due – Orange 61-90 Days Past Due – Red 91 or More Days Past Due – Red Step 1: Create a new calculated column in your data source which applies a numeric value to each header type that you would like to have highlighted. We have created a Calculated Column using the following query. Step 2: Select the Matrix to which you want to apply the formatting and go to conditional formatting section in the Format Tab and turn the Background Color Option “On”. Step 3: The Conditional Formatting is applied for different fields in the Values section in the Matrix. So we will apply conditional formatting according to No.  field first. Select Format by “Rules”. In Based on field select “Sum of Color Column” and in Summarization select “Sum”. In the Rules section add the Rule as shown in the Screenshot.  Step 4: Apply the other rules for different colors same as above. Step 5: The Colors have been applied to different buckets according to our rule for “No.” Column. Step 6: Repeat the same steps by selecting different fields from the drop down under Conditional Formatting, one by one. Step 7: Thus we have colored the different rows of the Matrix successfully based on our condition.

In this blog I am going to explain you how to create a Date table in Power BI desktop. A date table is a table that contains one column of DateTime type with unique values. It’s also called CALENDAR table A data model typically contains a date table A date table is designed to slice dates It should contain all dates included in the period to analyse It should contain one column of DateTime type Each day should be represented by a single a row There should be no gaps in the data It should be marked as a date table Using a date table with only the relevant year is best practise It’s recommended to have a rich set of additional columns Give the data model the flexibility to perform time intelligence calculations by using specific DAX functions Slice data by using one of its columns Create relationships between a date table and other tables in the data model Filter and group data across multiple tables Types of Date tables There are two ways to create a table Automatic Date Tables Power BI automatically creates a date table for each Date or DateTime column in the model. This makes it possible to slice each date by year, quarter, month and day. Calculated Date Tables It can be done by using two DAX functions: CALENDAR and CALENDARAUTO Syntax: Dates = CALENDAR(<StartDate>,<EndDate>) Dates = CALENDAR (      FIRSTDATE(Table[column]),    LASTDATE(Table[column])        ) Calendar functions return a table with a single column named “Date” and a list of values for the dates. Calendar requires the boundaries of the set of dates. CalendarAuto searches among all the dates in the data model and automatically finds the first and last year referenced within the model. Dates = CALENDARAUTO() The final formula for the calculated table should look something like this: Date = ADDCOLUMNS ( CALENDAR (DATE(2018,1,1), DATE(2025,12,31)), “DateAsInteger”, FORMAT ([Date], “YYYYMMDD”), “Year”, YEAR ([Date]), “Monthnumber”, FORMAT ([Date], “MM”), “YearMonthnumber”, FORMAT ([Date], “YYYY/MM”), “YearMonthShort”, FORMAT ([Date], “YYYY/mmm”), “MonthNameShort”, FORMAT ([Date], “mmm”), “MonthNameLong”, FORMAT ([Date], “mmmm”), “DayOfWeekNumber”, WEEKDAY ([Date]), “DayOfWeek”, FORMAT ([Date], “dddd”), “DayOfWeekShort”, FORMAT ([Date], “ddd”), “Quarter”, “Q” & FORMAT ([Date], “Q”), “YearQuarter”, FORMAT ([Date], “YYYY”) & “/Q” & FORMAT ([Date], “Q”) ) Once the date table is in place, it is a good idea to mark it as a date table as shown here: This both simplifies the code to author time intelligence calculations and automatically disables the auto Date/Time feature. I hope this will help you.  

Power BI Embedded is a Microsoft Azure service that is useful for the ISVs and developers to embed visuals, reports and even dashboard into the application. As Power BI Embedded is a PaaS analytics solution which provide Azure based capacity, Power BI embedded charge customers on an hourly basis there are n annual commitment for the Power BI Embedded service. As Power BI Embedded charges on hourly basis and there is no direct Auto Scaling feature available on Azure but, we do have API provided by using which we can scale the capacity. In this blog we are going to see how scale the Power BI Embedded capacity using PowerShell script. Before going to start we’ll first quick list the set up the prerequisites: You will need an Azure account, if you are implementing the PowerShell script for your organisation then you must have co-administrator role assign kindly keep in mind that if you have contributor role assign then you’ll not be able to make Automation account.(we’ll see about the Automation account in the later part of this blog.) Power BI Embedded subscription. Automation Account. I’m assuming you already have Azure account along with the subscription for the Power BI Embedded. Steps:- Create Automation Account:- Automation account is use to manage the Azure resource across all the subscription for the given tenant. To create Automation click on the create resource in your Azure portal as shown below and search for Automation account. Or you can type in search box Automation Account. 2. Click on create Automation Account and make sure to fill the following details. If you have multiple subscription then make sure to select proper subscription from drop-down. Make sure create Azure Run As account is selected to Yes (if you are co-administrator or administrator then it will by default selected to Yes). Once we create Azure automation account it will show under automation account. 3. Open the Automation account and go to the Connections and add below connection and types as shown below (Click on Add a connection and type the name and type as shown below) 4. For the AzureClassicRunAsConnection set the CertificateAssetName to AzureRunAsCertificate. 5. Add the Power BI Embedded subscription to your resource group. 6. Once we have Automation account ready go to the Runbooks under Process Automation in Automation Account. Runbook is useful for the routine procedures and operations. We can also use Azure Function app instead of Runbook. 7. Click on the Create a runbook and use fill following details. 8. Once we open runbook make sure to import the Module AzureRM.PowerBIEmbedded which can be installed by going to Module under Shared Resources then click on Browse gallery and search for the AzureRM.PowerBIEmbedded module. 9. Use the below PowerShell script which can also be found on the Power BI discussion site. $resourceGroupName = “<your resource group>” $instanceName = “<Power BI embedded instance name>” $azureProfilePath = “” $azureRunAsConnectionName = “AzureRunAsConnection” #”PowerBIAutoscale” $configStr = “ [ { Name: “”Weekday Heavy Load Hours”” ,WeekDays:[1,2,3,4,5] ,StartTime: “”06:45:00″” ,StopTime: “”23:45:00″” ,Sku: “”A4″” } , { Name: “”Early AM Hours”” ,WeekDays:[0,1,2,3,4,5,6] ,StartTime: “”00:00:00″” ,StopTime: “”04:44:00″” ,Sku: “”A1″” } , { Name: “”Model Refresh”” ,WeekDays:[0,1,2,3,4,5,6] ,StartTime: “”04:45:00″” ,StopTime: “”06:45:00″” ,Sku: “”A3″” } , { Name: “”Weekend Operational Hours”” ,WeekDays:[6,0] ,StartTime: “”06:45:00″” ,StopTime: “”18:00:00″” ,Sku: “”A3″” } ] “ $VerbosePreference = “Continue” $ErrorActionPreference = “Stop” Import-Module “AzureRM.PowerBIEmbedded” Write-Verbose “Logging in to Azure…” # Load the profile from local file if (-not [string]::IsNullOrEmpty($azureProfilePath)) { Import-AzureRmContext -Path $azureProfilePath | Out-Null } # Load the profile from Azure Automation RunAS connection elseif (-not [string]::IsNullOrEmpty($azureRunAsConnectionName)) { $runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName Add-AzureRmAccount -ServicePrincipal -TenantId $runAsConnectionProfile.TenantId ` -ApplicationId $runAsConnectionProfile.ApplicationId -CertificateThumbprint $runAsConnectionProfile.CertificateThumbprint | Out-Null } # Interactive Login else { Add-AzureRmAccount | Out-Null } $fmt = “MM/dd/yyyy HH:mm:ss” # format string $culture = [Globalization.CultureInfo]::InvariantCulture $startTime = Get-Date Write-Verbose “Current Local Time: $($startTime)” $startTime = [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($startTime, [System.TimeZoneInfo]::Local.Id, ‘Eastern Standard Time’) Write-Verbose “Current Time EST: $($startTime)” $scheduleTimeMidnight = ($startTime).Date Write-Verbose “Schedule Time Base (Midnight): $($scheduleTimeMidnight)” $currentDayOfWeek = [Int]($scheduleTimeMidnight).DayOfWeek Write-Verbose “DOW: $($currentDayOfWeek)” $stateConfig = $configStr | ConvertFrom-Json #| Select-Object Sku, WeekDays, Name, StartTime, EndTime #, @{Name=”StartTime”; Expression={[DateTime]:Smiley TonguearseExact($_.StartTime, $fmt, $culture)}}, @{Name=”StopTime”; Expression={[DateTime]:Smiley TonguearseExact($_.StopTime, $fmt, $culture)}} Write-Verbose “Writing Config Objects…” foreach($x in $stateConfig) { Write-Verbose “Name: $($x.Name)” Write-Verbose “Weekdays: $($x.WeekDays -join ‘,’)” $x.StartTime = ($scheduleTimeMidnight).AddHours([int]$x.StartTime.Split(“{:}”)[0]).AddMinutes([int]$x.StartTime.Split(“{:}”)[1]).AddSeconds([int]$x.StartTime.Split(“{:}”)[2]) Write-Verbose “Start Time: $($x.StartTime)” $x.StopTime = ($scheduleTimeMidnight).AddHours([int]$x.StopTime.Split(“{:}”)[0]).AddMinutes([int]$x.StopTime.Split(“{:}”)[1]).AddSeconds([int]$x.StopTime.Split(“{:}”)[2]) Write-Verbose “End Time: $($x.StopTime)” } Write-Verbose “Getting current status…” # Get the server status $pbiService = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName switch ($pbiService.State) { “Scaling” { Write-Verbose “Service scaling operation in progress… Aborting.” end } “Succeeded” {Write-Verbose “Current Status: Running”} Default {Write-Verbose “Current Status: $($pbiService.State)”} } Write-Verbose “Current Capacity: $($pbiService.Sku)” # Find a match in the config $dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } # If no matching day then exit if($dayObjects -ne $null){ # Can’t treat several objects for same time-frame, if there’s more than one, pick first $matchingObject = $dayObjects | Where-Object { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1 if($matchingObject -ne $null) { Write-Verbose “Current Config Object” Write-Verbose $matchingObject.Name Write-Verbose “Weekdays: $($matchingObject.WeekDays -join ‘,’)” Write-Verbose “SKU: $($matchingObject.Sku)” Write-Verbose “Start Time: $($matchingObject.StartTime)” Write-Verbose “End Time: $($matchingObject.StopTime)” # if Paused resume if($pbiService.State -eq “Paused”) { Write-Verbose “The service is Paused. Resuming the Instance” $pbiService = Resume-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -ResourceGroupName $resourceGroupName -PassThru -Verbose } # Change the SKU if needed if($pbiService.Sku -ne $matchingObject.Sku) { Write-Verbose “Updating Capacity Tier from $($pbiService.Sku) to $($matchingObject.Sku)” Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku } } else { Write-Verbose “No Interval Found. Checking current capacity tier.” if($pbiService.Sku -ne “A2”) { Write-Verbose “No Interval Found. Scaling to A2” Write-Verbose “Updating Capacity Tier from $($pbiService.Sku) to A2” Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku } } } else { Write-Verbose “No Interval Found. Checking current capacity tier.” if($pbiService.Sku -ne “A2”) { Write-Verbose “No Interval Found. Scaling to A2” Write-Verbose “Updating Capacity Tier from $($pbiService.Sku) to A2” Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku } } Write-Verbose “Done!” 10. Above script not includes Capacity pause, we can add that in the script. 11. Once we done with the script click on Save and the Publish the script. 12. Create the Schedule under the Shared Resources … Continue reading Auto scale the Power BI Embedded capacity using Job Scheduler in Azure →

In this blog we are going to embed a Power BI report into webpage, to do so below are the steps, 1. Go to https://app.powerbi.com/ and create new report or we can publish already created report, in this case, we have one sample report, which contain three pages Page1: Team Scorecard Page2: Industry Margin Analysis Page3: Executive Scorecard 2. Open the report to be embedded and Go to File ->embed Upon clicking on Embed, we will get following popup, which contain link that can be used for embedding and HTML code. 3. Now we have to paste the html code into HTML page, we can edit the iframe height and width 4. Now open the HTML page in the web browser then we can see the Power BI Report, in this, we can apply the filter, when we load the HTML page then we need to sign in. In this way, we can embed Power BI reports into Webpages.