BI-RoundUp – Power BI (Help Improve Power BI – Gateway Update for May)

Fortunately, there has not been a lot of activity in the Power BI space, which gives me a chance to catch up on some reading (Beginning DAX with Power BI  by Phil Seamark) and to have a bit of a rest.

Here are the updates for this week.

Help Improve Power BI

The Power BI Team has a survey out, in which they ask some questions to better understand how people use Power BI. There are also some sections where you can put in your own comments and help improve Power BI.

You can find the blog post here: Help Improve Power BI

Gateway Update for May

The monthly update for the On-Premise Data Gateway is here. And in this month’s update there are updates to Single Sign On (SSO) for Impala and SAP Hana.

As well as the Mashup Engine has been updated to match the May version of Power BI Desktop

All the details are here: On-premises data gateway May update is now available

Power Query Pattern – Adding Spaces in Text within your data with Camel Case

In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column.

To get this to work for you, all that you do is need to make one change to the code.

Below is what the data looked like

Then I created the following Power Query Pattern below.

#"TRANSFORM - Camel Case" =
Table.TransformColumns(
#"Removed Columns3",
{{"Operation",
each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "), type text}})

 

  • To use this pattern below are the following changes that you will need to make it work in your Power Query Editor
  • Line 1
    • This is my step name
  • Line 2
    • This is where I am using the Table.TransformColumns
    • NOTE: Even though this does appear to only be used for transforming columns, it works for data within a column.
  • Line 3
    • This is referring to the previous step name, which is returning the table contents
  • Line 4
    • This is my column name where I want to add in the spaces. As with my image above the column was named “Operation”
    • NOTE: This is the only part of the pattern that needs to be changed.
  • Line 5

What I did to get this to work is I was in my Power Query Editor Window

I then clicked on Advanced Editor in the Home ribbon

I then added this step into my code as shown below.

I then clicked on Done

I went back to my column and I could now see the data with the spaces after each capital word

Conclusion

As you can see I have demonstrated how Powerful Power Query (see the multiple use of Power!) is to get the data in the shape that you require.

If you have any suggestions or comments please let me know.

BI-RoundUp – Power BI (Dashboard Theming – Incremental Refresh)

Another week has flown by and there are once again more Power BI Updates, which I really enjoy. But at the same time there is so much content and blog posts that at times it is a bit of a struggle to keep up with what everyone is doing. And with all of that I am still really enjoying being in this space.

Power BI – Dashboard Theming

As you can see in the image above, you now can theme your dashboard. I think the key difference here is that even though you could theme your report there was no parity between the dashboard and the reports.

Well now this is no longer the challenge. Not only can you select default themes or colouring, but you can also customize different areas of the dashboard. As well as put in a background image, which could even be a GIF!

All the blog post details are here: Announcing Dashboard Theming in the Power BI Service

Power BI – Incremental Refresh

As you can see above there is a blog post with a video from Guy in a Cube in which he talks to Christian Wade and how incremental refresh works.

I highly recommend if you are looking to use incremental refresh to watch the video. They go into some detail about how to configure and use incremental refresh.

Not only that but in the blog post they announce some upcoming features,

  • The first of which I am very excited about incremental refresh in the shared capacity.
  • There were also be the functionality to update the meta data for your data which will not invoke a full refresh of the data.
  • Increased data sizes based on your capacity size.
  • Override the effective date, which means that you could set it to another date, especially if your data is loaded at a later time and not every day.
  • XMLA end points will be enabled, which means that you can connect to your Power BI Incremental refresh dataset with SSMS (SQL Server Management Studio) which will enable you to do things you can currently do with Azure Analysis Services or SSAS (SQL Server Analysis Services) Tabular 2017. For example, run scripts to refresh certain partitions.

You can find all the details here: Incremental refresh in Power BI Premium

Power Query – Renaming Multiple Columns

I was working on a dataset where I wanted to change multiple column names using one step and not having to change them manually. Since there were over 30 columns this would be really time consuming.

Below I detail how to complete this in the Power Query Editor, which will replace all the column names for me in one step.

This once again shows how powerful the Power Query Editor can be for ETL and automation tasks!

  • This is what the Original Column names looked like
  • To get this working, I had to go into the Advanced Editor and manually add a step.
  • Here is the Syntax that I added with my Step Name

    #”Rename Column Names” = Table.TransformColumnNames(#”Changed Type”, (columnName as text) as text => Text.Replace(columnName, “Sales – “, “”))

  • As you can see above my previous step name was called #”Changed Type”
  • Then the only other change I had to make was for the Text.Replace and what I was searching for and what I wanted to replace it with.
    • With this example I was looking for “Sales – “, “”
      • As you can see I searched for Sales – and replaced with “” (which is blank).
  • And this is what it looked like after manually adding in the above step.

As I have shown above a quick and easy way to rename multiple columns at once.

You can find the sample file here: Renaming Multiple Columns.pbix

As with every blog post if there are any comments or suggestions please leave them in the section below.

BI-RoundUp – Power BI (Power BI Update May 2018 – Custom Visuals in Excel – Service & Mobile Update – ArcGIS Online & Plus Subscriptions )

Here is my weekly BI-RoundUp and it is great to see that the monthly update for Power BI is here, as well as some other details of the new items.

Power BI – Update for May 2018

As always here are the updates for Power BI

Reporting

As shown above the first item is that you now can have conditional formatting based on another measure.

As well as having the colors for fonts also be on another measure. This means that you could have a color scale on an item for the measure and another set of rules for the Font color.

Along with that when looking for the alternate measure to use, you can search for the measure which is very handy.

There now is an update to Synch slicers where you can now create a group which will you to control how those groups are synced. You can also put slicers from different fields into one group

There have been improvements to the Log axis to make it consistent.

There have also been data label options updates for Funnel charts where you can now get additional options in order to view more label data

You can now also change the stroke width for line charts and combo charts, so that as shown below if you are using markers you can have them appear to be sitting in mid air, but in reality the stroke width is set to zero

Analytics

There has been a great update to the Drill Through feature, where now you have the option to take all the filters on your source page and pass them through to your drill through page. And you easily have the option to decide how you would like it to work.

Not only that you can also use measures as part of the drill through. If you do use measures it will automatically pass through all the filters from your source page.

Power BI Premium

If you have Power BI Premium, you now can use incremental refresh to only refresh your latest data. This is a great improvement in that if you have a large dataset you no longer need to refresh the entire dataset every day. You can easily configure it with a Date/Time Start and End Range. After which in the dialog for the table you can then configure how long you want to store all your data. And then what part of your data you would like to be incrementally refreshed.

Custom Visuals

There is the collage custom visual

And a Chinese Color Map

Data Connectivity

The biggest update is to the Web Connector from Examples, which now allows you to get data from a website that is potentially not stored in a table.

This allows you to type in some text for an item that you are looking for that is repeated, and Power BI will then find that item and extract the data you are looking for. This is insane!

The following connectors have either been created or updated

  • Common Data Service for Apps connector (beta)
  • Azure KustoDB connector
  • Google BigQuery and Azure HDInsight Spark connectors now generally available
  • Adobe Analytics connector update – Support for multiple domain logins (preview)
  • Visual Studio Team Services connector update – Analytics views support
  • OLE DB connector update – Support for alternate Windows credentials
  • SAP BW DirectQuery connector updates – Improved technical name support

Data Preparation

There has been an update to the Column from examples where you can now combine values from multiple columns to derive your new column

Here are all the details for the entire blog post: Power BI Desktop May Feature Summary

Excel – Custom Visuals

This is a big change and movement for Excel users. I know that having the option to not only import custom visuals, but to also develop them for a company’s exact requirements is super cool and enables them to view their data with the requirements.

Along with this it also means that there is the capability to create a Custom Visual once which can then be leveraged in Power BI or Excel.

You can read all about it here: Excel announces new data visualization capabilities with Power BI custom visuals

Power BI – Service & Mobile Update

There have been some updates to the Power BI Service with regards to Audit logs are now on by default. Which is great as this can assist with viewing who is using the reports, as well as how they are being used.

The On-Premise Data Gateway now has support in the personal version for Custom data Connectors, which is going to add tremendous value to Power BI.

In the mobile app you can now drill up and drill down. And there is a demo showing how the Power BI Mixed reality app might be applied in an organization.

The blog post details are here: Power BI Service and Mobile April Feature Summary

Power BI – ArcGIS Online & Plus Subscriptions

There now are the two new capabilities using ArcGIS maps.

The first is support for secure reference layers hosted in ArcGIS Online. And the second is the new organizational purchase of Plus subscriptions for ArcGIS Maps for Power BI.

What the above is it allows you to access additional ArcGIS functionality once you have signed in with your ArcGIS account.

If your organization uses an Organizational purchase of ArcGIS this can also be now used within Power BI.

There are a lot more details in the blog post which you can find here: Esri ArcGIS Online and Plus subscription organizational purchase are now available for ArcGIS Maps for Power BI

DAX – Concatenating Values only when values are selected

What I am doing with the DAX below is to only show the concatenation of values if a value is filtered. If nothing is filtered (Selected) then display text to show that nothing has been selected, otherwise display in a concatenation what has been selected.

NOTE: If you individually have to select each item, it is still being filtered so it will show all the values concatenated.

Selected User =
VAR ConCat =
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Power BI Audit Log'[User] ),
            'Power BI Audit Log'[User],
            ","
        )
    )
VAR IsItFiltered =
    IF ( ISFILTERED ( 'Power BI Audit Log'[User] ), ConCat, "No Users Selected" )
RETURN
    IsItFiltered

 

  • What is happening above with the Variable called “ConCat” is where I am concatenating the items from the ‘PowerBI Audit Log’ table and using the [User] column.
  • Then in the next Variable called “IsItFiltered” I am using the IF and ISFILTERED DAX function to evaluate if the [User] column is being filtered.
    • If it is being filtered then return the previous Variable called “ConCat” otherwise return “No Users Selected”
  • And the final piece is where I am returning the output from the IsItFiltered Variable
  • This is what it looks like when No users are selected
  • And then when two users are selected

As always if there are any questions please leave them in the comments below.

BI-RoundUp – Power BI (On-Premise Gateway Update – Developer Community Update)

Here is this week’s BI-RoundUp. I am hopeful that by this time next week the latest version of Power BI Desktop will be released!

Power BI – On-Premise Gateway Update

By far the biggest news is the capability to use Custom Data Connectors in the Personal Versions of the On-Premise Data Gateway. The Power BI Team did indicate in the blog post that support in the Enterprise version will be coming in a few months. This is fantastic to see it being made available.

There now is single sign on support using Kerberos for SAP Business Warehouse Server

And finally, there is the updated version of the Mashup Engine to the April version of Power BI Desktop

Here is a link to the blog post: On-premises data gateway April update is now available

Power BI – Developer Community Update

As you can see above for the Developer community update there now is the capability to leverage Custom Report Tooltips and the Q&A explorer.

There is now also new Azure Resource Metrics which allows for better metrics to understand how your Power BI Embedded application is working with the following metrics.

  • It will show Memory usage being used
  • It will show when there is Memory Thrashing, which is defined as when a report is trying to be run, and it needs to evict another dataset in order for your dataset to be loaded into memory. And this only applies to imported datasets.
  • It will show QPU High Utilization which shows the Query Processing units every minute on your resources when it is about 80%

There is now the capability to set up alerts on your Azure resource.

Finally, there is a new learning channel to create custom visuals with a hands-on lab.

All the details of the blog post are here: Power BI Developer community April update

Reactivated Players DAX Pattern

I had a requirement where my customer was looking to gain some insights into their data relating to reactivated players.

The definition used for a reactivated player is a player that has previously played but has not played for a certain number of days. The player then comes back and plays again. So, by this definition the player is reactivated.

What I had to do, to get this working so that it performed well was to put this into a calculated table. This ensured that I only had the data I required. Due to the sheer volume of data that I had to go through, trying to do this with a DAX measure proved in my example to be very slow. And I would rather create a calculated table which can perform much faster and give a greater reporting experience.

I explain below. It is quite Complex so please read carefully below with the colour coded sections

Overview: What this calculated table is doing, is counting how many days the player made an order between his last order and second to last order based on date.

NOTE: I am going to explaining this from the inside out.

Using either Power BI Desktop or Azure Analysis Services or SSAS Tabular 2017, I created a Calculated with the following syntax below.

  1. Reactivated Players Table =
  2. FILTER (
  3. CALCULATETABLE (
  4.    ADDCOLUMNS (
  5.     VALUES ( ‘Adventure Works'[Customer Name] ),
  6.      “Days Between Orders”
  7.       , CALCULATE (
  8.         DATEDIFF (
  9.          CALCULATE (
  10.           MAX ( ‘Adventure Works'[Order Date] ),
  11.           FILTER ( ‘Adventure Works’, ‘Adventure Works'[Order Date] <> MAX ( ‘Adventure Works'[Order Date] ) )
  12.          ),
  13.          MAX ( ‘Adventure Works'[Order Date] ),
  14.         DAY
  15.        )
  16.     ),
  17.   “Last Order Date”, CALCULATE ( MAX ( ‘Adventure Works'[Order Date]), ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) ),
  18.   “GeographyKey”, CALCULATE (
  19.         MAX ( ‘Adventure Works'[GeographyKey] ),
  20.         ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) )
  21.     )
  22.  ),
  23.  [Days Between Orders] <> BLANK ()
  24. )

          Lines 8 – 11
    • This DAX Calculation is getting the second to last date the player made an order.
      • This could be called “Second to Last Order Date”
    • Line 12
      • This DAX calculation is getting the last date the player made an order.
        • This could be called “Last Order Date”
      • Lines 7-8 & 13-15
        • This is the outer DAX CALCULATE which is using the DATEDIFF to count how many Days it was between the two dates above.
        • EG: DATEDIFF(“Second to Last Order Date”,” Last Order Date”,DAY)
      • Line 6
        • This is the name of the column that I am creating in my Calculated Table called “Days Between Orders”
        • Line 5
          • Here I am getting the VALUES for the column which is the Customer Name
          • NOTE: It is important here to always use the Customer Name or CustomerAccountKey in order to get back the values you are expecting
        • Line 17
          • I am creating another column called “Last Order Date” with another DAX measure
          • This column is getting the last time the player made an order. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Date table in my dimension model
          • NOTE: This is so that I can then use the Date table to filter multiple tables when accessing the reports.
        • Line 18 – 21
          • Once again I am creating another column called “GeographyKey” with a DAX Measure
          • I created this column, so that I can get the GeographyKey for each Customer Name. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Geography table in my dimension model
          • NOTE: This is so that I can then use the Geography table to filter multiple tables when accessing the reports.NOTE: If I wanted to create any more columns to link back to the Dimension tables they would be done in this section.
        • Lines 3-4 & 22-23
          • This is where I am specifying to ADDCOLUMNS to my CALCULATETABLE syntax. Which creates the in-memory table.
        • Lines 2,24-25
          • The final piece is what makes this table so fast. Is I am then filtering my entire table. This is so that I do not bring back records that I am not interested in.
          • I do this specifically in line 24, where I am using the column I created in lines 6 – 16 and filtering it out to say where the Days are NOT BLANK

Apologies for the formatting above, I just could not get it the way it was in Word!

Once the above is done, I then get back a subset of all the records, which then makes this table very fast to query.

Creating the Dynamic Days Between Reactivation table

To give the required flexibility to the client, I created a table using the What-If Parameters.

This then allowed the client to dynamically decide how many days there should be for a player to be deemed as Reactivating.

I then created a slicer for this table

Reactivated Players

This measure is created off the table created above. Which makes it run correctly. As well as being super-fast when being queried.

Reactivated Players = 
VAR SelectedValue =
    SELECTEDVALUE ( 'Days between Reactivation'[Days between Reactivation], 30 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Reactivated Players Table' ),
        'Reactivated Players Table'[Days Between Orders] >= SelectedValue
    ) 
  • In this measure above, I am once again using the Days between Reactivation Slicer for the end user to determine how many days decide how long a player is re-activated from.
  • Then I am counting the rows where it is less than the Number of days between the bets.

Report in Action

Below is what the report looks like in action, where the user is using the Slider in order to determine how many players are reactivating.

NOTE: The reason that I put in the column called “Reactivated Players” is so that it shows that the measure is working as expected. So if the Slicer is set to 30 any player with “Days Between Orders” of less than 30, should not have a 1 next to them. And as the slider goes up, the players with the corresponding “Days Between Orders” should decrease or show less “1”

 

 

Conclusion

I have shown how to create the reactivated players DAX pattern. As well as allowing the user to dynamically select the period for reactivation.

As always, I welcome any comments or suggestions.

Here is a link to the PBIX file: FourMoo – Reactived Players.pbix

Resetting Page back to Defaults using the New Buttons Feature and Bookmarks in Power BI

In this blog post I am going to show how to use the new Buttons feature to reset your page back to all the defaults, using the new Buttons and Bookmarks

I have started implementing this on report pages and find it very handy for end users to just reset the page and not have to concern themselves with de-selecting everything.

Adding and configuring the button

The first thing that I had to do was to add the Reset button and then configure it.

  • I clicked on Buttons and then selected Reset
  • I then configured my button with the appropriate title so that the users would know that they can use this button to set everything to default.
  • Below are the settings that I configured it as

Configuring the Reset Bookmark

The next step I did was to create the reset bookmark. This is what how I wanted the page to look at its default state.

In my example I wanted the TopN City to be set to 10

  • The first thing I did was to ensure that it was set to 10.
  • NOTE: I also ensured that there were no other filters, or slicers being applied.
  • I then went into the Ribbon, clicked on View and then selected Bookmarks
  • Then in the bookmarks pane, I clicked On Add
  • I then clicked on the three dots … and selected Rename.
    • I renamed it to Reset to TopN10 – Reset to Default
    • NOTE: The reason that I did this, was so that I know that this particular bookmark is for this page.

Assigning the Bookmark to my Reset Button

I now needed to assign the bookmark I created above to my button, so that when clicked it will reset it back to the bookmark (which makes it appear that it is resetting it to a default state)

  • I clicked on my Button.
  • Then in the settings I scrolled down to Action and configured the Type to Bookmark and the Bookmark to the bookmark I created earlier.

Testing the Resetting to Default

Now as you can see below in the GIF, it now resets to default when I click on the button.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Conclusion

As you can see I have demonstrated how to use the new Buttons and Bookmark feature to reset a page back to defaults.

As always if you have any comments or suggestions please leave them in the section below.

BI-RoundUp – Power BI (Drill up, Drill Down Mobile App – USA Facts on Financial Data)

Here is the weekly BI-RoundUp, not too much going on this week, which gives me a little time to breathe!

Power BI – Drill Up, Drill Down on Mobile App

The Power BI Mobile App has been updated to so that now it is a lot easier to drill up or drill down on the visuals on the mobile platform.

Next on the backlog is the drill through which I am interested to see how that will work.

You can find the details here: Drill Down & Up in Power BI Mobile apps

Power BI – USA Facts on Financial Data

As you can see above there is a blog post that relates to providing a comprehensive view of the combined US federal, state and local governments’ revenues and expenditures.

There is quite a lot of details in this report and if you are interested in more details please click on the link to the blog post below.

USAFacts breaks down government financial data with Power BI