Embedding Power BI Reports in a Webpage

The ability to embed and publicly share Power BI reports is an attractive feature. The process seems straight-forward. There are important things to note, some legal, some functional. Lets start with the boring legal stuff, covered in detail on the Power BI website here

In short, be aware of the following:

• This is about public, open access sharing of data. Think before you embed...
• This is Pro-license feature.

Now for the good stuff, actually doing it. To begin decide on what content you want to embed.

The content has to be a report, currently individual visualisations or dashboards can't be embedded. Create a new report with the required content on it, the suggestion here is to ring-fence embedded content from content distributed by other means. Also, as any applied update to embedded reports automatically syncs to destination web site you'll probably want to leave this report alone once it has been embedded.

From the View menu within the report choose the appropriate view mode for the content being embedded. Some experimentation may be required here. The visuals at the end of this post are rendered using Dynamic view mode.

Now generate the embed code from the File menu.

Read and digest the messages, before proceeding. Eventually you will be provided with some html code, an example below with the unique identifier removed.

Width, height, frameborder and allowFullScreen are all configurable. To get the result required for this blog post all that was needed was to tweak the width slightly.

This is still a preview feature... the time taken for a change to sync across from the Power BI report to the destination web-site was a little hard to predict and also some distance from near real-time.

The control is pleasant enough to work with, especially in full-screen mode. All interactivity built into the report such as drill capability in the example above are available within the embedded window or in full screen mode.

Exciting as this is, imagine how much fun developers are going to have exploiting the possibilities offered up by this announcement.

More on this soon.

Dynamic Dates and Calendars

A common requirement when building a dashboard is the ability to set a value (typically a date or time value) dynamically and use that value as a filter for a dashboard, or part of a dashboard.

Lets start with a simple dataset; as always links to download the sample data and finished pbix file will be available at the end of the post.

The first step to achieving this is a calendar table, or dimension. There are a number of sources out there for generating or downloading a calendar table. I was drawn to this method as it involves generating a date table dynamically within the Power BI model using DAX.

Start by choosing Add Table from the Modelling tab and providing the following in the formula bar:

Calendar =
CALENDAR ( "01-01-2016", "31-12-2016" )

The invoked CALENDAR function will generate a range of dates, based on the start and end dates supplied as arguments.

Then, using the ADDCOLUMNS function, extend the created table to include whatever date derivations are required.

Calendar =
ADDCOLUMNS
(
CALENDAR ( "01-01-2016", "31-12-2016" ),
"DateAsInteger", FORMAT ([Date], "YYYYMMDD")
)

Finally, extend the function to provide columns that identifies the dynamic date(s) required to make the dashboard work as required.

Calendar =
ADDCOLUMNS
(
CALENDAR ( "01-01-2016", "31-12-2016" ),
"DateAsInteger", FORMAT ([Date], "YYYYMMDD"),
"Yesterday", if([Date] = today()-1,1,0),
 "LastSevenDays", if([Date] >= TODAY()-7 ,
if([Date]<= TODAY(),1,0))
)

The last two items add flags to the Calendar table based on the TODAY() function. The flags will update dynamically whenever the dataset is refreshed.

To leverage this very useful table first create a relationship between it and the imported Sales table. From the Relationship view accessed from the left toolbar drag Date from the Calendar table over Sale Date in the Sales table to create the relationship. Power BI should detect a relationship which looks like when inspected.

The relationship type detected should Many to One (*:1)

Back in visualisation mode, create a simple chart by dragging Sales Amount and Date on the design canvas. Set the Axis to just use date...

A rather cluttered chart will emerge as it is rendering a data point for every date in the dataset. To set the chart to just show the last seven days drag LastSevenDays from the Calendar table into the Visual Level Filter section of the graph. Set the filter to only show values where LasdtSevenDays is 1.

This technique can be employed at visual, report or dashboard level.

Samples:

To ensure this sample works regardless of when it is viewed, LastSevenDays HardCoded which provides literal dates for the seven days the chart discloses has been added. It is this value which is used to filter the visual.

Also provided are a number of common date derivations that expose common values used in a calendar. To ensure items like Day sort correctly there are a number of numerics included, Day Number, Week Number, etc. These are used as sorts for Day, Week etc

Download the sample workbook here. The .pbix file, including the DAX generated calendar is here.

Cumulative Columns and Row Subtraction

I recently worked on a project which involved taking a csv file and pushing it into Power BI. Nothing unusual here, you would think. The challenge was the data in the file being provided. The only value presented was cumulative, on a day by day basis. What was required was what happened each day.

As there was no opportunity to change the upstream process, I needed a way of doing this in Power BI. What follows is a step-by-step to deriving the actual column above within Power BI. At the end of this post is a link to the source file I used and the completed pbix file.

To begin, import the file and call the table source. Import the file again and call it lookup. Now, the interesting stuff starts...

Go into edit queries, it should look a little like this:

With the Source query selected, go to Add Column on the ribbon and choose Add Index Column and then From 0. Now choose the Lookup query and do the same again, this time starting the index From 1. Close and apply the changes.

Back in the designer view, click edit relationships from the icons on the left bar, which should look a little like this:

To create a relationship between index in Source and index in Lookup simply highlight the field in Source and drag onto the field in Lookup. Hovering over the created relationship you should see index highlighted in both tables.

More complex relationships can be defined if required, but a 1 to 1 relationship is sufficient for this purpose.

Now for the fun bit, creating a DAX calculation that will fetch the cumulative value from the lookup table and subtract it from the cumulative value in the source table. The relationship between the two indexes defined earlier means that we are effectively deducting the next row from the previous row in the source dataset.

To begin click new column from the Modelling tab, make sure you have the source dataset highlighted, this is where the new column will be created and also sets a context for the query:

Now to  create the DAX formula in the calculation bar, which looks like this...

Actual = Source[Cumulative] - SUMX(RELATEDTABLE ('Lookup'), [Cumulative])

Actual is the name provided for the new column. Source[Cumulative] returns the Cumulative column from the source table. SUMX(RELATEDTABLE ('Lookup'), [Cumulative]) returns the Cumulative column from the lookup table. You should end up with a new column called Actual that looks like this.

One last thing to tidy up, the last row of the source dataset returns -70 in the actual column, which is going to mess up any visualisation we plot against it, so let's get rid of it. From the Home tab go to Edit Queries. Choose Remove Bottom Rows from the Remove Rows drop-down. Enter 1 to remove just the last row and that's it.

Samples:

You can grab a sample XLSX file here. The completed pbix file is here.