BI-RoundUp – Power BI (Working with PowerShell in Power BI – Deprecation of Custom Visuals API 1.1)

Here is the weekly BI-RoundUp and at least this week it will be a quick read!

Power BI – Working with PowerShell in Power BI

I am not going to go into great detail here, due to Kay giving a detailed explanation how to use the PowerShell cmdlets.

What I will say is that after reading through the above blogpost and with the Power BI Team creating the PowerShell Modules, this means that you can install the module for Power BI, which will then allow you to leverage all the module items. I have been using PowerShell for a while and having the modules is a lot easier to use and get out the data you want.

I suggest reading through the blog post below, as I am certain there is something you will learn and can use in your organisation.

Blog Post link: Working with PowerShell in Power BI

Power BI – Deprecation of Custom Visuals for API 1.1

As shown above there will be a deprecation of support for the Custom Visuals API version 1.1

This will happen from 01 September, so I suggest people who have got old Custom Visuals read this blog post with the links on how to upgrade their Custom Visuals

Here is the blog post: Announcing the deprecation of custom visual’s API version 1.1 and older

DAX (Power BI) – Dynamic TopN made easy with What If Parameter

In this post I am going to demonstrate how to create a Dynamic TopN slicer using a What-If Parameter.

This will allow your user to simply use the Slicer/Slider to view the TopN values and as a bonus if the user slides it to zero, it will display everything!

Who doesn’t like something that is easy to create, but makes it so much easier for the user to gain insights into their own data easily and quickly?

Example

For this example, I am going to be using a [Sales Amount] measure from my Orders table.

Next for the TopN, I am going to be looking for the TopN by City. This is a key component when identifying what you want your TopN to be based on.

NOTE: If the explanation of the TopN Measure can be a bit complex, if that is the case, you can either copy the code and modify it for your requirements. Or you can view the animated GIF further below so you can see it working.

Creating the TopN Slicer

  • In Power BI Desktop I went to the Modeling tab and then clicked on new Parameter
  • I then gave it the following properties as shown below.
  • One thing to NOTE is that I set the Minimum to 0 (zero)
  • When you are creating this TopN What-if parameter, you can change anything for the Maximum, Increment and Default.
  • I could then see the table created on the right-hand side.
  • As well as the Slicer on my reporting canvas, which I formatted

Creating the TopN Measure

  • Below is the TopN measure, which I will go into detail how it works below.
TopN City = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Sales Amount],
    RANKX ( 
            ALLSELECTED(  'Orders'[City] ), 
            [Sales Amount]
                )
                  <= SelectedTop,
        [Sales Amount]
)

 

  • Line 2, is where I created the Only Variable called SelectedTop
    • This is getting the selected value from the Slicer.
    • If it is slid to 5, this variable SelectedTop will store 5.
  • Line 4 is where I used the SWITCH(TRUE()
    • What this does, is it enables me to pass multiple statements to evaluate in one DAX function.
    • NOTE: You could possibly do this with an IF statement, but I prefer doing it this way, because quite often the requirement changes to have more than 2 conditions, so doing in this way it is easy for me to add another condition.
  • Line 5 is my first condition, where I have said if the TopN Slicer (SelectedTop)= 0 (zero) then display all the [Sales Amount]
    • It will do this because there is no filter context being applied on the [Sales Amount]
  • Lines 6 – 9 is where the Magic happens and uses the values from the TopN slicer.
    • This is also the second condition for the SWITCH(TRUE()
      DAX expression.
    • Even though this is for TopN values, I use the RANKX to achieve the desired result from Line 6
    • Next, I am using the ALLSELECTED, because in my table I want to select the TopN for the City values.
    • Line 7 is where I am selecting from my table Orders and the column called City ALLSELECTED( ‘Orders'[City] ),
    • Line 8, is where I am specifying my measure for the RANKX which is the [Sales Amount]
    • Line 9, is where I am closing off the RANKX function.
  • Line 10 is where I am now comparing it to be less than equal to the selected slicer value SelectedTop
    • If this evaluates to TRUE, then display the RANKX up to and including the selected slicer value.
  • Line 11 is the ELSE condition for the SWITCH(TRUE()
  • Line 12 is closing off the SWITCH DAX Function

TopN Slicer in Action

Please watch the following animation below in which I will demonstrate by using the Slider I can to from Top 10, to Top 5. And then by sliding it to 0 (zero) it will show all the cities.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Conclusion

As I have shown, by using the What-If Parameter and some DAX there is now a way to easily create a dynamic TopN that is easy for your users to use within their reports.

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

Power BI – How to do Pagination in Power BI Reports

In this blog post I am going to demonstrate how to achieve Pagination in Power BI reports, using the new Bookmarking feature. I got this question from a follower on Twitter Murali Krishna

With the new bookmarking feature there now is a way to make it appear that you can page through Power BI reports.

Below is what it looks like in action.


Parameters

I used the following Parameter as shown below, which I then used later to automatically determine how many rows to show per page

How I got the Page Numbers based on the Rows Per Page Parameter

In this next section, what I did was to use my parameter with my data so that I could determine the page numbers dynamically.

By using the parameter this enabled the data to always get the right number of rows for each page dynamically.

  • This is what my data looked like at the start.
  • The first thing that I did was to add an Index by going into the Add Column ribbon and clicking on Index Column and then From 1
    • NOTE: The reason that I started at 1, was because when I am showing the rows it starts from 1
  • I then duplicated the Index Column, so that I could then use this to create the page number.
  • Next, I clicked on my column called “Index – Copyhighlighted below in Yellow
  • Then in the Transform ribbon I clicked on Standard and then Integer-Divide
  • I then manually put in a value of 10
  • Next, I wanted to change it from a static value to use my parameter value, so I did it by doing the following.
    • This is what it looked like with the Static value in the M Code
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, 10), Int64.Type}})
    • I then changed it from 10, to my Parameter value, as shown below highlighted in PURPLE
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, #"Rows Per Page"), Int64.Type}})
      • NOTE: The Parameter name is case sensitive.
  • The final steps was I added 1 to my column, so that once again the page number started from 1. And then I renamed it to reflect the page name
  • This is what it looked like once complete.
  • I now loaded this into my data model.

Creating the Pagination

I am not going to go into every step due to it will make this blog post too long, but I will demonstrate how I got the Pagination working.

  • The first thing that I did was to show the Bookmarks Pane.
  • Next what I did was to show my data based on the page number.
    • What this did is it enabled my data to be dynamic
      because it was based on the parameter, if I had to change how many rows to show to 25, due to the way I modelled the data in the Query Editor it would then have 25 rows per page.
  • I then created 4 bookmarks as shown below, with each being linked to a page number in the filter section.
  • I then also created measures to display the page that I am on.

    Pagination = “Page ” & SELECTEDVALUE(‘Sheet1′[Page Number],1) & ” of ” & [Max Page Number]

  • As well as the Records Measure

    Record Rows = ” Records ” & [Start Index] & ” to ” & [Max Index] & ” of ” & [Max Index Records]

  • Now the final piece, which does take some time to get right, was to enable the buttons to go forwards and backwards. So that when the user clicks on the buttons it appears that they are going forwards or backwards on each page.
    • I had to enable the Selection Pane Window
    • I had created an image for a Forward button and a Backwards Button
    • I then inserted my Forward Image.
    • NOTE: Always make sure that you are on the Bookmark Name where you want the interaction to take place.
      • I then clicked on the Forward Image and went to the Properties.
      • I then set the properties for the Link to type Bookmark and to Page 2 as shown below.
      • Then I went to the Bookmark called Page 1 and clicked on Update
      • I then went to the Bookmark called Page 2
        • I then went to the existing image and clicked on Hide
        • I then imported my Forward Image again.
        • I then clicked on the Forward Image and went to the Properties.
        • I then set the properties for the Link to type Bookmark and to Page 3 as shown below.
        • I then imported my Backward Image.
        • I then clicked on the Backward Image and went to the Properties.
        • I then set the properties for the Link type to Bookmark to Page 1 as shown below.
      • I then had to repeat this for all the other pages, as well as hiding the images on the different pages.
      • As well as ensure that the images were in the identical place as per the other images. This made it appear as it was the same button.
  • I then finally uploaded this to the Power BI Service and tested it to ensure that it worked as expected.

Conclusion

As you can see I demonstrated how to do Pagination in Power BI, it is a bit complex to get it right. But what I really like is that when it is done right, it appears seamless to the end user and VERY easy to use. Which is always my goal to make the end result as simple as possible.

Below is the report in action, if you want to click through and test it.

Power BI – Using Reports Usage Metrics to create Custom Reports & Leverage them for other App Workspaces

In this blog post below I am going to show you how I created my own Custom Usage Metrics Report.

NOTE: This has been done using my Report Metrics and not the Dashboard Metrics.

NOTE II: I am going to refer to the Reports Usage Metrics as RUM (It has a nice ring to it)

And then how I changed the Connection details, so that I could re-use the same Custom Usage Metrics report in all my different App Workspaces.

(more…)

BI-RoundUp – Power BI (Desktop Update for June, Service Updates for June, Dashboard Email Subscriptions) – Excel (Get & Transform Updates June 2017)

Here is my weekly BI-RoundUp with what has happened and what is new.

Power BI – Desktop for June

Finally the Power BI Desktop July update is here. So here are the details below of the new updates.

Reporting

The table and matrix visuals are now generally available.

Not only that but they have also made some additional enhancements which are you can now drill down on columns or rows.

There is also an update to the default style which makes it easier to read based on best practice.

As well as finally they honor the word wrapping if you have text that goes onto a second line, it will keep it on that second line.

Next is the ability to rename the fields in your reports. When renaming your fields it creates an Alias so your existing fields that you get from your data will remain the same.

There now also is the Custom Visuals store integration directly in the Power BI Desktop. I think that this is a much better experience instead of having to go to the browser and open to find a custom visual. As well as now you can also get your Custom Visual Certified which will mean that it is verified by Microsoft Custom Development team.

There are also new Custom Visual updates which is in the blog post. I am interested into looking at the Drilldown Player.

You can now also use the Relative Date Slicers in the Filters section in Power BI Desktop. I use the Relative Date slicers quite often so it is great to see the same functionality in the Filters section also.

Next is the responsive layout for Visuals. This is another amazing feature in that the layout will respond for your visual based on how large or small it is. This means that it will show the information you are trying to portray in the most efficient way possible. And they can currently be used on the following Visuals. And that you can disable this responsive layout if required.


 

 

 

 

 

 

 

 

 

There is a new waterfall option where you can specify the breakdown which then show you that breakdown field causing the increasing of decreasing between each neighboring categories.

Analytics & Reporting

This month there are two new Quick Measures that have been added, which are the Star Rating and Concatenated List of Values.


Bidirectional cross filtering for DirectQuery is now Generally available also.

Data Connectivity

The Snowflake connector is now Generally Available also.

Query Editing

There have been some additional enhancements to the “Add Columns from Examples” in the Query Editor. In this months update they have added support for Number and Date/Time/TImezone data.

Also you can reference their documentation article which will be updated in due course here: Add a column from an example in Power BI Desktop

Blog Link

You can find all the blog details with a YouTube video here: Power BI Desktop July Feature Summary

Power BI – Service Updates for June

There have been quite a few updates to the Power BI Service in the last month.

Some of the highlights are now the new unified API, which you can now use not only for API access, but also for Power BI Embedded.

There is also an update in the Dashboard tiles for the relative links which enables you to direct the tile to go to a specific report. As well as the Impala connector.

Also there is an updated version for the Personal Data Gateway, which now will enable you to delete it from the Power BI Service, as well as enhancements for speed and reliability.

And finally they have made some performance improvement for the Power BI Mobile App.

You can find the details here: Power BI Service and Mobile June Feature Summary

Power BI – Dashboard Email Subscriptions

You can now subscribe to daily emails on your Dashboards. This is really an awesome new feature so that you can now get a daily snapshot of your dashboard emailed to you.

More details are here: Introducing dashboard email subscriptions: a 360-degree view of your business in your inbox every day

Excel – Get & Transform Updates June 2017

It is great to see that there are a whole host of new features in the Get & Transform updates in Excel. As mentioned previously these features are often enabled in Power BI Desktop, so if you are looking for some additional help or documentation include Power BI in your search to find solutions.

Here are the new updates for June 2017

  • New transformation features:
    • Add Column by Example.
    • Split Column (by Delimiter/Number of Characters) into rows.
    • Basic mode for Group By operation.
    • Extract Text Before/After/Between Delimiters.
    • Unpivot Only Selected Columns.
  • Combine Files experience—ability to reference first file as example.
  • New Go to Column in Query Editor.
  • SAP HANA Connector—enhancements to Parameter Input UX.

As you can see above there are quite a lot of new features, I have been using the Columns by Example since it has been released and I find that it not only enables me to get the data that I want quicker. But it also enables me to learn new M functions that I could potentially use in other transformations. The Split Column update is also handy when you need data to be split into rows. As well as the Extract Text before or after or between delimeters as I have found often that I want something inbetween.

You can find all the details here: June 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

BI-RoundUp – Power BI (Office 365 Adoption Content Pack – Embedded Accelerator) – Excel (Get & Transform Updates May 2017)

This will no doubt be a quieter week, leading up towards the end of May and into June when there will be the next wave of updates from the Microsoft Power BI Team

You might also notice that I have changed the name from BI-NSIGHT to BI-Roundup, as there was another blogger who has his blog URL as BiInsights, so rather than make it confusing I thought to change the weekly update name.

(more…)

Power BI – Free vs Pro Infographic

I have been active on the Power BI Community Page for quite some time, and what I have often seen is people not quite sure what options are free and what options require a Pro license.

So here is my infographic in which I have put down which options are free and which options will require a Pro license.

I am hopeful that people will find this useful in understanding which options are free and which options require a Pro license.

If I have left anything out, or something is wrong please let me know and I will update it.

As well as I will keep this infographic up to date as there no doubt will be some additions to the Power BI Service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can access the images from the following link: Power BI Free vs Pro Infographic – Latest Version

Power Query – Adding Parameters within a piece of text

Below is where I had a situation where I wanted to put in a parameter within a piece of text. This was so that I could then dynamically change the Month Version for my budget and when I refreshed my data it would then use my specific Month Version. This was due to the requirement being that they wanted the ability to select ANY Month Budget Version.

Example

  • I had a requirement where I wanted to use a parameter value, but it would form part of a complete part of text. And as shown below in this example it would be for Mar (March)
  • I wanted the Output to look like the following:
    • Budget_Mar_YR1
  • And the part which is part of the variable is highlighted in BLUE above “Mar
  • I also wanted to re-use this for multiple conditions later in my script.
    • I required it for the following:
      • CY – This is for the Current Year
      • YR1 – This is for the following Year 1, so if I am in 2016 it would be for Year 2017
      • YR2 – This is for the following Year 2, so if I am in 2016 it would be for Year 2018
      • YR3 – This is for the following Year 3, so if I am in 2016 it would be for Year 2019

Solution

In order to do this, I went into the Advanced Editor in the Query Editor.

The name of my Parameter was calledBudget Version“, so when using it in the Advanced Editor it would be used with the following syntax below.

#”Budget Version”

In the section below is where I now defined by additional conditions, so that they would be dynamic. An explanation will follow afterwards.

BudgetVersionCY = “Budget_”&#” Budget Version”&“_CY”,

BudgetVersionYR1 = “Budget_”&#” Budget Version”&”_YR1″,

BudgetVersionYR2= ” Budget _”&#” Budget Version”&”_ YR2″,

BudgetVersionYR3= ” Budget _”&#” Budget Version”&”_ YR3″,

As you can see above each line was compromised of the following:

  • I defined our name highlighted in RED
    • BudgetVersionCY
  • Then I started with what our name was, which is highlighted in BLUE
    • “Budget_”
  • Next is where I inserted our Parameter highlighted in PURPLE
    • &#” Budget Version”&
    • NOTE: When you want to add additional TEXT or parameters you have to open it with the ampersand “&” as well as close it off (or end it) with an ampersand “&” also.
  • And then finally I added some more text at the end highlighted in BLUE again.
    • “_CY”

Then later in my query is where I put in my conditional statements into my Conditional Column as shown below.

#”Filtered Rows” = Table.SelectRows(#”Added Custom2″, each ([Budget Version] = #”
BudgetVersionCY
” or [Budget Version] = #” BudgetVersionYR1” or [Budget Version] = #” BudgetVersionYR2” or [Budget Version] = #” BudgetVersionY3“)),

Final Note

Just one thing to note, is when I put in the following syntax into my Query Editor you will lose the capability to edit it by using the settings or Gear icon

BI-NSIGHT – Power BI (Microsoft Flow & Power BI, Webinar How Microsoft Handles Power BI)

I have no doubt that as the year moves closer to the end of the year there will be slightly less news and updates. But with that being said here are this weeks updates.

Power BI – Microsoft Flow and Power BI

I have been personally looking at Microsoft Flow lately and it can really automate many tasks, as well as do things for you. And it is very easy to use Microsoft Flow.

And now they have added the Data Alerting from Power BI into Microsoft Flow. This is really awesome, because what it now means is that you can leverage all the capabilities from Microsoft Flow when there is a data alert.

For example you can now forward the alert to an email group. Or do a whole host of other options in Microsoft Flow.

You can read all about it here: Turn insight into action using Microsoft Flow and Power BI

Power BI – Webinar on How Microsoft Handles Power BI

This looks to be an awesome Webinar and one that I will definitely be watching. It is going to be great to see how they go about making it work so well.

You can find the details here: Power BI Webinar 11/29 How Microsoft BI Team manages Power BI