Microsoft has provided a free, downloadable or embeddable, Power BI Report sample using COVID-19 data for the US. This report can be used as is, or customized to leverage the COVID-19 data and report capabilities to compare to your regular business data.
About the report
You can use the embed code below to embed this report wherever you would like:
Make note, you may need to adjust the iFrame size.
Or, you can download the PBIX file itself and make your own customizations and bring in your own data here: download the .pbix file (here).
Report Capabilities
Filter by Date
Select your data range
Filter cases by State
Click on the State you would like to select in the left hand chart.
Watch as the visuals filter to that states numbers.
Drill Through to the County View
Right click on the left hand visual for the state you would like to select.
Drill Through to County View
View the Metrics by County
Customize the Report
Choose the data you would like to bring in (in my case, we want to view account and sales numbers from Dynamics 365 Sales – I’m not going to walk through all the transformations we made here)
Create your visuals
Save your report
Publish to Power BI Service (show in Power BI service)
Configure a scheduled refresh for your data sources
Share in Teams or SharePoint to distribute (image shared in teams)
When building a Power BI report, it’s important to take screen space into consideration. You don’t want an overcrowded report canvas that makes it tough for your decision makers to find the actionable insights they need to make their decisions.
That being said, a valuable skill to learn in Power BI is showing or hiding visuals at the click of a button. Follow the steps below to achieve this.
Step 1: Add a couple of visuals to your canvas
For the sake of my demo, I’ve added 2 visuals of different types to opposite ends of the canvas. One is a pie chart, one is a bar chart.
Step 2: Add two blank buttons to the canvas
Add each button above the visual you want to show with that button. In my example I am naming one button Pie Chart and one Bar Chart.
Step 3: Open the Selection Pane from your View menu
This is an important and rarely-used feature of Power BI. The selection pane allows you to see all the visuals on your screen, and set their visibility.
Step 4: Hide one of the visuals
Click on the visibility icon next to the visual you would like to hide.
Step 5: Open the bookmark pane
Another little used feature of Power BI. Read more about the capabilities of bookmarks here. For the purpose of this demo, we are using them to hide and show different visuals on the canvas.
Step 6: Create a new bookmark
This will essentially take a screenshot of what your report page looks like at the time of adding the bookmark, you can always update it later. Make sure to name your bookmark something useful, and uncheck the “Data” item.
Step 7: Hide the other visual and show the one you previously hid
Go back to your selection pane and hide one visual while showing the other.
Step 8: Take another bookmark
Follow the same steps as in Step 6.
Step 9: Add a bookmark action to the buttons that you have on the canvas
This is where the canvas will come to life, now your visuals will be hidden and shown based upon the users click of the buttons. Hold the control button and click the buttons to test out your creation!
I hope you found this helpful and can apply it to your next report!
Recently, a client requested a daily digest email be sent to their support staff with a list of their active tickets, along with a link to the particular record. This blog will take you step-by-step through the usefulness of this Flow and how I created it.
What’s great about this Flow is, even though in my example I am triggering it as a recurrence with CDS as a data source, you can use any type of trigger you may need or any data source that applies. The important—and more difficult—part is including the links directly to records in an email table.
Challenges
You might think this would be a simple request, but it turns out that you need to do a little bit of thinking to make this work. My first instinct was to try to store the returned values for each user as an Array Variable, but when you use the “Create HTML Table” action based upon the array, the link reference does not get recognized as HTML and you get an email that looks something like below:
How to Create this Flow (or something similar)
Create the Flow with a Trigger of Recurrence. Set the recurrence pattern to whatever you would like. We are setting a daily recurrence in this example.
Initialize an integer variable for the count of records returned. We don’t want to send users emails with blank tables.
Initialize a string variable for storing each new row of the table.
List All Active Users
Apply to each user
List tickets
Apply to each ticket
Increment count variable
Condition: If count is greater than 0
Get account (to display the client’s name)
Append to string variable for a new table row. This is how we are building the HTML for the email.
Condition: Count is greater than 0
Send an email from a Shared Mailbox. Formatting the rest of our HTML will happen in the body of this email action. Insert whatever table formatting you may want into the <style> tag. Keep in mind, in my screenshot the web version of outlook strips some of the formatting.
Set count to 0
Set ticket table string to null
With any luck, your results should appear like this:
One of the key benefits of using Microsoft’s Common Data Service is its highly customizable and robust permissions settings. With CDS, you can design a functional system tailored to your business. In order to do that, however, you need to understand the building blocks of this security architecture. Follow along for for a basic understanding of everybody’s favorite topic: security! In this blog, I will be covering the standard security model in CDS. In a later blog, I will cover hierarchy security.
Take a look at this diagram below of a basic business structure. It will be important in understanding how security is applied to entities.
In this example, we have set up an organization that includes the following breakdown of what the Sales department architecture may look like.
Aerie (Organization Level)
Users in this business unit need to view data from all across many business units. Typical users are the C-Suite and System Admins
Sales, Ops, Finance (Child Level 1)
The first level down from the Org business unit is the major departments at the company. Typical users here are the V.P.’s of the department.
East, Central, West (Child Level 2)
The Sales department has many divisions, and we may need to permission the divisions to only see their account information. In this case, we have an East, Central and West division. Typical users at this level of the hierarchy are Sales Territory Managers.
Enterprise Accounts, Small and Mid-Size Accounts (Child Level 3)
Each Sales division has sales people who target Enterprise or Small-Mid size accounts. We may need to permission their edit capabilities to each others data. In this case, the typical user would be a Sales Person.
Business Units
If your organization is structured around departments or divisions that have separate products, customers, and marketing lists, you might want to create business units. Business units are mapped to an organization’s departments or divisions. Below are the characteristics of Business Units.
Roles created in your environment’s default business unit replicate through all
Every user must belong to one business unit and users can be assigned to any business unit in the hierarchy
This is important, because you need to assign the security role the right permission based on if they need to access just that particular business unit’s records or the records of that business unit and its child business units.
Entity Ownership
Entities can either be owned by the organization or users/teams. You configure this setting when creating the entity. Once created, it cannot be changed.
Organization-Owned
Entities owned by the organization. A security role can either have permission to these, or not.
User or Team-Owned
There are user or team-owned system entities. Because these records are owned by a user or team, they’re connected to a business unit and specific security roles for the business unit. Therefore, these entities participate in role-based security.
Security Roles
A security role defines how different users, such as salespeople, access different types of records. To control access to data, you can modify existing security roles, create new security roles, or change which security roles are assigned to each user. Each user can have multiple security roles. Security roles are characterized by the following:
Grants permissions to entities
Grants permissions to use Model Apps
Each user can be assigned multiple roles, so for each user they are given the highest level of permission their combination of roles allows.
Permission Items are as follows (CRUD, Append, Append To, Share, Assign)
None: User does not have permission to perform an action.
User: User has permission to perform the action for records that they own.
Business Unit: User has permission to perform the action for records owned by a user or team in the business unit they are in.
Parent: Child Business Units: User has permission to perform the action for records owned by a user or team in the business unit they are in and any child of that business unit.
Organization: User has permission to perform this action on any record in the system.
Actions:
Create: action of creating a record.
Read: action of viewing a record and its metadata.
Write: action of editing a record and its metadata
Delete: action of deleting a record (point of no return).
Append: action of changing the lookup field values on a record.
Append To: action of changing the lookup field on a record for a particular entity.
Assign: action of changing the owner of a record.
Share: action of sharing a record with another user so that they can view or edit.
Teams
A team template can be used for the entities that are enabled for automatically created access teams. In the team template, you have to specify the entity type and the access rights on the entity record. For example, you can create a team template for an account entity and specify the Read, Write, and Share access rights on the account record that the team members are granted when the team is automatically created.
Access to Forms
Forms also have the ability to be hidden or shown to users based upon security roles. Sometimes you may need to design a form for one type of user versus another.
Understanding these important facets of CDS Security will help you determine how your users will interact with your system in a productive and secure way.
