Power BI Service Administrator Role

In order to administer Power BI, there is a role in Office 365 that allows a user to be a Power BI administrator without having to have higher admin rights in Office 365. This role is called Power BI Service Administrator. To assign the role, log into https://portal.office.com and log into the Admin portal. Select Edit a User to edit an existing user: Select the user and next to Roles select Edit: Click Save. To do … Continue reading Power BI Service Administrator Role

Row Level Security (RLS) in Power BI

In Power BI you have the ability to implement row-level role-based security. What this means, is you can restrict data to certain users based on what role they are in. Here, we will go through an example of implementing role-based security. Let’s start with our data set. A simple Accounts table with a State field: In Power BI Desktop, import the data. Then go to the Modelling tab and you … Continue reading Row Level Security (RLS) in Power BI

Power BI PubNub

To create a Power BI streaming dataset using PubNub, perform the following steps. Firstly, log into powerbi.com. In the bottom left of the screen, select Streaming datasets: Then select + Add Streaming Dataset: Select PubNub: Enter in information to connect to PubNub: Click Create: Go to the dashboard you want to see the data and select + Add tile: Select Custom Streaming Data: Select our dataset, PubNub Sensor and click … Continue reading Power BI PubNub

Power BI Desktop Data Refresh Issue

I had an issue recently where the data in my dataset was not making it’s way to the Power BI Desktop report. Basically, there were some records I was looking to see on the report but they weren’t there. I looked at the report filters and could not see anything that would restrict the records. I had added a new column to the dataset, which appeared successfully on the report, but the data just … Continue reading Power BI Desktop Data Refresh Issue

Display Power BI Tiles in Microsoft CRM

Microsoft CRM now has the ability to embed Power BI tiles. This means, Microsoft CRM users are able to view Power BI report charts directly from within Microsoft CRM, without having to leave the application. To do this, perform the following steps. Firstly, enable Power BI in Microsoft CRM. To do this, go to Settings->Administration->System Settings and go to the Reporting tab: Set “Allow Power BI tile embedding to “Yes” Create a … Continue reading Display Power BI Tiles in Microsoft CRM

Using DAX PREVIOUSYEAR

We can also perform time intelligent functions on our date fields in DAX. Let’s compare this year’s sales to last year’s sales. We will create a new measure for previous year sales. First, let’s create a date table. This is created in Power BI Query Language (M): Next, create the measure. We are calculating the sum of the extended price, in the previous year, using the PREVIOUSYEAR function: What this means, is … Continue reading Using DAX PREVIOUSYEAR

Using DAX Single Date Functions

Some DAX functions will return a single date. Let’s go through some of these with our dataset. STARTOFMONTH If we apply this to our data without a filter, it will find the start of the first month: LASTDATE ENDOFQUARTER Applying this to 2016, it uses the first 2016 record (09/02/2016) and gets the end of that quarter:  

DAX Calculated Columns

Let’s start with a dataset that has sales orders. There are sales order lines: And the data looks like this: However, there is no extended price in this dataset. To calculate it, let’s add a new calculated column. Click on the ellipse on the dataset: And select New column: Add the formula: The new column will appear in the dataset: And the new column can be added to our Power BI … Continue reading DAX Calculated Columns

Introduction to DAX (Data Analysis Expressions)

DAX, or Data Analysis Expressions, is a library of functions, formula and query language used in analytics. It is used in: Power BI SSAS Tabular models (SQL Server Analysis Services) Power Pivot in Excel To write DAX formulas in Power BI, open Power BI Desktop and go to your dataset. In the example below we have Transactions: Click on the Ellipse next to the dataset and you will see “New measure” … Continue reading Introduction to DAX (Data Analysis Expressions)

Using DAX YEAR

DAX has various date functions we can use to help us visualize data. Let’s go through an example. In our dataset, we have order lines and we have the orders associated with them. Orders have a date field, i.e. when our customer placed the order: We have an active relationship in Power BI matching our orders and lines: We can derive the year, month, day, hour, minute and second from … Continue reading Using DAX YEAR