How to Sort by Month Year Name in Power BI report

When you create Power BI report based on Month and Year calendar, the default sorting i s based on the alphabetic name like then screenshot below.

If we need to create a report that will display the month and year combination and sorted by the time, you will need to create a different column and apply the sorting. Here are the steps.


First you will need to crate a new column like below:
MonthYearOrder = CONCATENATE(MyCalendar[Year],right(CONCATENATE("00",MyCalendar[MonthNumer]),2))

Then click the column you need to display like "MMM_YYYY" and select "Sort by column". Pick the new column "MonthYearOrder" just created.

In the report, you will need to pick either acceding or descending to display the sequence correctly.

You could use this method to apply the sorting on other columns like "Department" based on the different order you prefer to display.

Power BI Streaming Dashboard example

There are many different ways to present real data report and dashboard in Power BI. One of the way is to use PubNub that could make your data streams available on the Microsoft Power BI platform.  Here is the detailed steps to use PubNub for Power BI.

The first step is to create a Power BI dataset from Power BI service.

The second step is to create the dataset by connecting to PnbNub. The data example is on the PnbNub site. You need both Channel and Subscription Key to connect.

The third step is to create the dashboard from Power BI service. You should select the "Streaming dataset".

The forth step is to add tiles to the report as in the below screenshots.

There are some gotchas you should be aware of the Power BI streaming. Please read Microsoft blog for details.

There are few different ways for Power BI streaming, you could also use Flow automation to push data to Power BI dataset.

Create summary table and ensure no empty value for Power BI

If you need to create a summary table for Power BI report, you should be careful not to return empty value. Here is one example.

NoActionTakenApprovalTasks = SUMMARIZE(DimApprovalTasks,
   DimApprovalTasks[Approver],
   DimApprovalTasks[ApprovalRole],
   "Total Tasks", countrows(DimApprovalTasks),
   "Total No Action Taken Tasks",  IF(
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")= BLANK(),
0,
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")
)
)


In this example, we add an IF condition to return either 0 or value if not empty. This will be same to create measure to ensure value set to 0 when empty value returned. An example is below.

Total Approved Tasks =

IF(

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

) = BLANK(),

0,

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

)

)

Power bi calculated column multiple if statements example

We have a requirement to display the percentage distribution in Power BI. The percentage can be any value from 0% to 100%. However, the report should show the percentage range like 10-20, 20-30, etc.

In order to do this, a calculated column with multiple if statements will be required. If you know the syntax, it will be quite simple. Here is the example.


Percentage Range =
IF([No Action Taken %]=0.00,"0",
 IF(AND([No Action Taken %]>0.00, [No Action Taken %]<=0.10),"1-10",
  IF(AND([No Action Taken %]>0.10, [No Action Taken %]<=0.20),"11-20",
    IF(AND([No Action Taken %]>0.20, [No Action Taken %]<=0.30),"21-30",
      IF(AND([No Action Taken %]>0.30, [No Action Taken %]<=0.40),"31-40",
        IF(AND([No Action Taken %]>0.40, [No Action Taken %]<=0.50),"41-50",
          IF(AND([No Action Taken %]>0.50, [No Action Taken %]<=0.60),"51-60",
            IF(AND([No Action Taken %]>0.60, [No Action Taken %]<=0.70),"61-70",
     IF(AND([No Action Taken %]>0.70, [No Action Taken %]<=0.80),"71-80",
   IF(AND([No Action Taken %]>0.80, [No Action Taken %]<=0.90),"81-90",
IF(AND([No Action Taken %]>0.90, [No Action Taken %]<1),"91-99", "100"
 
)))))))))))

There is another option to use switch you could also try.

Populate user office location for Power BI

There is a need to populate user office location for Power BI. Here is one of the quickest way to get this information trough AD Powershell.

I've quickly developed a script to have user office populate in a csv file and upload to SharePoint for Power BI to consume. Here is the script based on AD Powershell.


$ADUsers =  Get-ADUser -Filter *  -Properties physicalDeliveryOfficeName  | where physicalDeliveryOfficeName  -ne $null
$outputFile = "C:\Harry\Projects\SPCOE\Scripts\GetADUsers\Output\ADUsers.csv"

$rows = @()

foreach($ADUser in $ADUsers)
{
    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                                   

                                            Name = $ADUser.Name
                                            UPN = $ADUser.UserPrincipalName 
                                            Office = $ADUser.physicalDeliveryOfficeName                                       

                                            } | Select-Object Name,UPN,Office

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue

Power BI for LMS365 learning data

In order to get learning data from cloud LMS365, you could use either connector for Microsoft products like Logic Apps, Power Automate, and Power Apps or OData web services directly. For Power BI, you could use OData with API Secret Key for your tenant. The steps have been described in details by Microsoft. There are few tips you should be aware of.

1. You need to get the API Secret Key for your tenant from the support before you can use the API.
2. The user name normally is “api”
3. After connected to LMS365 through OData, you should not import all tables. You will get an error when you importing all tables.
4. Normally you only need the following enties.

• Course Catalog
• Course
• User
• Enrollment
• Assignment
• SCORM
• SCORM Statuses
• Quiz User Statuses
• Learning Module User Statuses

You should set up the refresh schedule using the same OData credential. The example of the report is below.

How to invoke Azure function inside PowerShell or Azure Runbook

The way PowerShell to call Azure function is same as call rest web service. There are few tips as below.

1. Use client object module and add the module into PowerShell or Azure Runbook
2. Azure function might have security configured and you will need to add "Access-Control-Allow-Origin"
3. You might need to pause the result but in our case it is simple "Yes" or "No"

The example below can give you quick start.

# Add references for Runbook

Add-Type -Path  "C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.dll"

Add-Type -Path  "C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.Runtime.dll"

$spoSiteUrl ="https://yourcompany.sharepoint.com/sites/pubs"

$creds = Get-AutomationPSCredential -Name 'O365_svc'

# Load SharePoint online CSOM for email

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($spoSiteUrl)

$userName = $creds.UserName

$password = $creds.Password

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

$ctx.Credentials = $credentials

$ctx.AuthenticationMode = [Microsoft.SharePoint.Client.ClientAuthenticationMode]::Default

$PubsId=12345

$RequestUrl='https://yurcompany.azurewebsites.net/api/PublicationLookup?code=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==&qry=canbepartiallyapproved&id='+$PubsId

write-Output $RequestUrl

$WebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession

$WebSession.Credentials = $Context.Credentials

$WebSession.Headers.Add("Accept", "application/json;odata=verbose")

$WebSession.Headers.Add("Access-Control-Allow-Origin", "mycompany.sharepoint.com")

 

$Result="No"

#Invoke Rest Method

$Result = Invoke-RestMethod -Method Get -WebSession $WebSession -Uri $RequestURL

write-Output $Result $pubs.Id 

Tips to filter Power BI report using query string parameters in the URL

With increasing usage of Power BI report, there is a case that users request to have a link on the SharePoint list to query all related information on the item. One example is as below.

One event list with all different events

Another publication list that have look up column linked to the event

The requirement is to have a link to the event list item that will display ALL the publications related to that event. The solution is to Filter a Power BI report using query string parameters in the URL as the screenshot below.

Here are some tricks and tips.

1. Filter query string parameters only works on  Power BI service or apps. It does not seem to work on the URL embedded in SharePoint Online or WebSite.
2. The query can work for hidden page, hidden fields
3. Table and Field names are case-sensitive, value isn't.
4. The query syntax is different for data type like Number or String. You need to add '' to the string value
5. The last part in report URL like ‘?noSignUpCheck=1’ need to be removed before adding the filter
6. You might need to change the special characters like space to %20

The example is like below.

This one will not work before removing the ‘?noSignUpCheck=1’.

https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1?noSignUpCheck=1?filter=FACTPubsRecord/PubsMeetingID%20eq%201549

This will work.

https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1?filter=FACTPubsRecord/PubsMeetingID%20eq%201549

You could use this URL to send specific query to end users or add the calculated column to SharePoint list.