Design a site like this with WordPress.com
Get started

Month: March 2019

Posted on

Power BI with Traffic Light Indicator

Today you will learn how can you implement a traffic light indicator functionality in Power BI.

Power BI allows you to conditionally format a column based on another column value. Traffic Light indicator also works based on a value which is provided in the given column.

I will be implementing the Traffic Light Indicator functionality in the same report which we created while learning Add hyperlinks to CRM record in Power BI.

Figure 1: Image of Add hyperlink to CRM records in Power BI post.

Let’s get started. You need to add a new column to your dataset as shown in the below image:

Figure 2: Adding new column to dataset
Figure 3: Created New Column

Rename the column as “Indicator” for better understanding.

After renaming use a Circle UNICHAR code 11044 next to Indicator for populating a Circle icon in the column. 

Indicator = UNICHAR(11044)
Figure 4: Using Circle UNICHAR code11044

Select the table and check the box which appear next to Indicator column under dataset. This will add the Indicator column under report.

Figure 5: Added Indicator column to the report.

The circle under Indicator column will appear in black color.

For enabling the traffic light capability, you need to right click on the Indicator column and select Conditional formatting –> Font color. This will change the Font color of circles.

Figure 6: Right click on Indicator column
Figure 7: Select Conditional formatting –> Font color

The below screen will appear, click on Based on field dropdown. Select the column based on which the traffic light indicator would work.

Figure 8: Font color default screen

In my case I am going to use Profit %(new_profit) column. Which is the third column in our table.

Figure 9: Select field based on which the traffic light indicator would work.

Go to Diverging and check the box which appear next to it. This will populate a new field “Center” between “Minimum” and “Maximum”.

Figure 10: Check the Diverging field

Select Number value under the dropdown which appear below Minimum, Center and Maximum fields. Provide the number range for the Indicator column based on this the traffic light functionality would work. Then click OK.

Note: The color bar which appear below the Diverging field is totally based on the colors which you select under the Minimum, Center and Maximum fields.

Note: I have taken the minimum number as 0 which mean the number which appear nearest to 0 under Profit % column will appear as brightest red and the number which is nearest to 70 will appear the brightest yellow. The number which appear somewhat near 50 will be a mix of Red and Yellow color.

Figure 11: Enter value range for Indicator

Now, you have got the functionality that you wanted. The Indicator column appear as a Traffic Light Indicator.

Figure 12: Traffic Light Indicator
Advertisement
Posted on

Add hyperlinks to CRM record in Power BI

Today, we are going to learn how to hyperlink CRM entity records using Power BI desktop.

Figure 1: Use hyperlink in CRM Power BI Report

For creating a hyperlink in Power BI report. Load the data from CRM entity.

I have used a custom entity having schema name as “new_powerbi” for this post. Select the columns which is require for your reporting purpose.

After that click on “New Column” button which appears on the menu bar or  you can right click on the “new_powerbis” data set and select New column option.

Figure 2: Create New column for hyperlink

This will open an editor where the mapping must be done for fulfilling the hyperlinking functionality.

Note: Before you proceed with hyperlink, make sure that you have a “primaryid” in your dataset. This is required for opening an individual record when a you click on the link without any conflict.

Figure 3: Create a New Column

Insert the values in the below format: 

Column = "https://orgname.crm8.dynamics.com/main.aspx?etn=entityschemaname&id={" & datasetname[primaryidcolumn] &"}&newWindow=true&pagetype=entityrecord"

EntitySchemaName – new_powerbi

DataSetName – new_powerbis (Refer below image DataSetName in marked with red) 

CRM URL = "https://samreen70.crm8.dynamics.com/main.aspx?etn=new_powerbi&id={" & new_powerbis[new_powerbiid] &"}&newWindow=true&pagetype=entityrecord"
Figure 4: Insert the value in New Column editor.

Clicking on enter will add a new column to your report. The column will contain the URL of each record in the form of text.

Figure 5: Fetch CRM URL in text format

For converting the whole big URL from text to hyperlink. You need to click on Data, go to Modeling tab.

Select CRM URL column and Click on “Data Category: Uncategorized” dropdown. Then select Web URL option. This action will convert the text url into hyperlink format.

Figure 6: Convert text URL to hyperlink format

Navigate back to Report. You will find that the text in CRM URL columns has been converted from text to hyperlink format.

Figure 7: CRM URL column having hyperlink URL

Now, you need to click in CRM URL column and Go to Format –> Values. As shown in the below image.

Figure 8: Option to convert link url to link icon

Clicking on Values you will find an option “URL icon” turn it On. This will change the entire column hyperlink url to a hyperlink icon.

Figure 9: Enable URL icon on CRM URL column

Clicking on the URL icon will redirect you to CRM entity record.

Posted on

Power BI with CRM Option Set

This article is about the workaround which a user can adopt. If user want to populated the CRM Option Set Label in report.

Power BI suite has a limitation that it shows the CRM option set Value not Label.

Please refer the previous blog(Power BI with Microsoft Dynamics CRM) for understanding how to establishing a connection between Power BI suite and Dynamics CRM for loading a data into Power BI.

Suppose, a you wants to create a report with Account Name and Region field.
Considering, Region is an option set field.

Figure 1: Creating a Table report

For getting the option set label, you need to create a dummy query where you need to map the Option Set Labels and Values.

Click on the Edit Queries button on the Menu bar. The below screen will pop up, right click on the Queries.
New Query –> Select Blank Query option

Figure 2: Create Query

A table would get created. Refer the below query format and paste it in the editor.

= #table(
{"Value","Label"},  
{  
{100000000, "APAC"},  
{100000001, "EU"},  
{100000002, "NA"}})
Figure 3: Table created for option set Label and Value mapping.

You can rename the table by right clicking on it. Choose Rename.
Click on Close & Apply button, select Close & Apply option.

Figure 4: Save the changes

This will load the new table with columns, and bring you back to Report.

After this you will need to establish a relationship between the Account and Region. Click on “Model” as shown in the below image in yellow box.

Figure 5: Manage Relationship between Account and Region table

Click on “Manage Relationships” button. Below screen will appear now click on “New” button for creating relationship

Figure 6: Create Relationship

Select the tables between which you want to create a relationship. Like Account and Region in my case. As I want to populate the Account Region option set label in the report.

Next click on the column which has the similar data in both the tables for mapping. Then click “OK” button.

Figure 7 : Mapping the common column from both the tables.

Clicking on “OK“. The below screen will appear where you can see the relationship which got created. Click “Close” button.

Figure 8 : Relationship established

Coming back to Model, you can now view a link between the two tables.

Figure 9 : Relationship established between Account and Region.

Click on Report and select the Label field from the Region Table.

Figure 10: Region option set Label populated.
Figure 11: Power BI Table Report

Posted on

Power BI with Microsoft Dynamics CRM

Download Power BI Desktop , it comes with both 32 bits and 64 bits. Gets this installed.

After the Power BI installation is completed it launches with the Welcome screen.

Figure 1: Power BI welcome screen

For establishing a connection with any data source, you will need to click on the Get data icon which is visible on the welcome screen. Else you can click on the Get Data button which is visible on the untitled page as shown in the below image:

Figure 2: Connect with Data Source

Click on More –> Online Services –> Dynamics 365 (online)

Figure 3: Connect with Dynamics CRM

Clicking on Connect button, a screen will appear asking for CRM Web API URL.

Figure 4: Power BI Web API URL

Login to CRM –> Go to Settings –> Customization –> Developer Resources

Figure 5: CRM Setting (Customization) page

Copy the link from Service Root URL which appears under Instance Web API section.

Figure 6: CRM Web API

Enter the copied CRM Web API link in Power BI, under Web API URL field. Click OK button. This required for establishing a connection between Power BI and your CRM Organization.

Figure 7: Power BI Web API

The below screen will appear when the connection gets established between Power BI suite and Dynamics CRM.

Select the CRM entities by clicking on the check box which appear next to them. This is required for pulling data only for the selected entities, then click Load button.

Figure 8: Connection established

This would take some time depending on the number of entities selected for loading.

Note: If you don’t want the entire fields from the entity to get loaded. You can narrow down the fields by click on Edit button.

If user clicks on Edit button, Query Editor Page will open. Click on “Choose Columns” button under Home tab.

Figure 9: Option to choose require columns for report

Select the require columns/fields which are required for reporting. You can search the field directly by entering the name of the field under Search Columns.

Figure 10: Select required fields for report

Clicking on OK button, the Query Editor will have only the selected field.

For reflecting the changes Click on “Close & Apply” button then select “Close & Apply” option.

Figure 11: Close & Apply for reflecting the changes.

Finally, the data gets Loaded. I didn’t filter the column while loading the entity as shown in Figure 8 therefore all the columns got populated as shown under red box.

Figure 12: Data Loaded for Entity

You can now start with your Power BI Report by selecting any option under Visualization.

Visualization brings our data into life in the form of amazing visual representations.

Posted on

Starting with Power BI

What is Power BI?

Power BI is a cloud based Data Visualization and Business Intelligence tool which help the users to read the data from different data source. It is a collection of services, apps, and connectors that work together to turn your unrelated sources of data into a unified logical format, visually immersive, and interactive insights.

Why Power BI?

Power BI allows quick and easy insight into the data which helps in developing interactive dashboard and reports. User can connect to the data whenever and wherever they want for analyzing and visualizing data.

Ways to use Power BI

  1. Power BI Service (Interactive Data Visualization BI Tools)
  2. Power BI Desktop (Desktop Version)

Let’s get started with Power BI using its desktop version. The reports created within Power BI Desktop can be publish directly from the Desktop application to Power BI Service.

Tasks which can be performed using the Power BI suite:

  1. Can get the data from different data sources.
  2. User can establish a relationship between the data for enriching their data model.
  3. Data can be visualized in many different format like Bar and column charts, Cards, Area charts, Gauge charts, Funnel charts, KPI’s, Maps, Tables, Matrix etc.
  4. Publish the report.