BI-RoundUp – Power BI (February Update, Data.World Connector)

There has not been a lot happening in the past few weeks, but I am delighted to have another blog post with the latest Power BI updates.

Power BI – February Update

As I do every month I will go through in fairly light detail the new features in Power BI. And there are some fantastic new features.

Reporting

The first is now the capability to multi-select items across multiple charts.

This is great because people can gain more insights because it will cross filter between what you have selected.

As well as if you are bookmarking any of your multi-select they will too be remembered as part of the bookmark.

The next update is another big one, which people have been asking for some time, is the ability to Sync slicers across different report pages.

It is very easy to set up and integrate into your reporting.

As Amanda mentioned in her video, you now could have a starting page, where you define all your slicers, which will then affect all the other reporting pages.

There has been an update to the Numeric Slicer, where now if it is a whole number data type for the column, it will only show the whole number.

Previously if it was a Whole Number data type for the Numeric slicer it would show it with the decimal points.

There is now also an improvement with the Bing Geocoding currently in the Power BI Service, which should render the points on the map quicker. It will come to the Power BI Desktop at a later time.

You can now also configure the overflow of data labels on bar and column charts.

This is great for when you want to show the entire number. It will still only display it when there is the required space to do so for larger items, smaller items as currently done will not be displayed.

When formatting or using the Analytics Pane, there now is an option to search for settings. This is great because the Formatting pane is getting more and more options, so this will make it easier to find what you are looking for.

You can now specify your own custom date table. This is great because it will work with all the existing Time Intelligence features, as well as the quick measures.

You will just have to ensure that it meets the Date table criteria.


The Quick measures is now generally available and no longer needs to be enabled as a preview feature.

Custom Visuals

There now is the capability for your organization to be able to define which Custom Visuals can be used by the Organization. This is great in terms of governance and compliance so that the report developers know that the Custom Visuals signed off by the Organization can be used.

There are a whole host of new custom visuals as shown below.

Network Visualization and Filter

Slope Chart by MAQ Software

Filter by List

as Timeline

as T-Accounts

Performance Improvements

There is a whole host of performance improvements, mostly aimed at DirectQuery for SQL Server.

  • DirectQuery performance improvements
  • Open and save performance improvements
  • “Show items with no data” improvements

Upcoming – Persistent Filters in Power BI Service

This is yet another feature that a lot of users have been asking for.

This will allow the report developer to define when a user comes back to an existing report if the filters that were set the last time the user was using the report will persist.

This is once again a great upcoming feature, which will make it a lot quicker and easier to view reports on a daily basis.

And finally, there is now an option to find Power BI Consulting services from Microsoft partners.

If you want more details, you can find the entire blog post here: Power BI Desktop February Feature Summary

Power BI – Data.World Connector

If you did not know there is a Data.World connector which hosts a whole host of publicly shared data.

The blog post from the Power BI team goes through and explains how you can easily leverage this data in Power BI.

As well as there being a Webinar on how to use the Data.World connector

Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

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 – Finding the data type when using Variables in Measures

I have been working with an Organization where I have had to create some rather complex measures. And within these measures I am using a fair number of variables.

I found that at times it was a challenge to understand what data type was being returned by the variable. Which at times would lead to a different result, because the Variable data type being returned was different from what was expected.

Example

For example, in my measure I wanted to get a date range, if the variable was not a date or date/time data type then my measure would fail.

So below I will show you how to easily find out what the Variable data type is.

Last Year Sales = 
VAR Date_MaxDate =
    MAX ( 'Orders'[Order Date] )
VAR Number_TotalDiscount =
    SUM ( 'Orders'[Discount] )
VAR Text_CustomerName =
    LASTNONBLANK ( 'Orders'[Customer Name], Number_TotalDiscount )
RETURN
    Date_MaxDate

 

  • Above is my measure and as it currently stands it will return the value for the variable Date_MaxDate which is created on lines 2 & 3.
  • Now I selected the measure name [Last Year Sales] under fields in Power BI Desktop.
  • I then clicked on Modeling in the ribbon and under Formatting it shows me that the data type is Date Time, as shown below.
  • The data type for my Variable called Date_MaxDate is Date Time.
  • If I now changed the result to be Number_TotalDiscount on line 9, it then shows me that the data type could be any numerical format
  • And finally, if I change the result to be Text_CustomerName on line 9, it then shows me that the data type is Text

Conclusion

As I have shown in my blog post above, a quick and easy way to see what data type is being returned by the variable.

If you have any comments or questions please leave them in the section below.

Power BI – One Measure dynamically made up from multiple measures (To show Month Estimate for current Month)

Where I am consulting there was a requirement where for the current month they wanted to display the Current Month Estimate for the current month they were in, and to show the historical sales for all the previous months.

This makes some sound business sense, since currently month would typically start off with a lower sales amount, which would increase as each day goes by. Whilst the calculation for the Month Estimate is not very complex it does show if the sales are inline of where they should be.

In my example below, I am going to demonstrate how I did this, so that to the end user it appears to be one measure, even though I am using a few techniques for it to be displayed correctly.

Working Example

In this example I have got some sales data, and I am going to demonstrate how to create the Month Estimate.

I will then create the [Sales Amount] Measure, and the [Current Month Estimate] Measure.

The [Current Month Estimate] is made up by this simple calculation:

  • Sales for the current month I am in.
  • Get the Days Gone in the current month I am in.
  • Get the Total Days for the current month that I am in.
  • For example, if my current date is 09 December 2017, then I would have the following details below.
    • Sales for Current Month = $36,819.60
    • Days Gone in Current Month = 9
    • Total Days in Current Month = 31

And then finally, I will show how create the magic measure, which will show the Sales up until the previous month (Nov 2017), and then for the current month (Dec 2017), it will display the measure [Sales Inc Est] which will be renamed to Sales Amount.

Creating the standard measures

Below I created the following measures:

Sales Amount = SUM('Orders'[Sales])
Days Gone in Curr Mth = 
VAR LastDataDate =
    MAX ( 'Orders'[Order Date] )
RETURN
    DAY (
        DATE ( YEAR ( LastDataDate ), MONTH ( LastDataDate ), DAY ( LastDataDate ) )
    )
Days in Current Month = 
VAR LastDataDate =
    MAX ( Orders[Order Date] )
VAR EndOfCurrMonth =
    EOMONTH ( LastDataDate, 0 )
RETURN
    DAY ( EndOfCurrMonth )
Current Month Estimate = 
DIVIDE ( [Sales Amount], [Days Gone in Curr Mth] ) * [Days in Current Month]
Mth Est (Inc Prev Sales) = 
VAR LastDataDate =
    MAX ( 'Orders'[Order Date] )
VAR Start12MonthsAgo =
    DATE ( YEAR ( LastDataDate ) - 1, MONTH ( LastDataDate ) - 1, 1 )
VAR LastDatePreviousMonth =
    EOMONTH ( LastDataDate, -1 )
VAR SelectedSalesPrevMonths =
    CALCULATE (
        [Sales Amount],
        FILTER (
            'Date',
            'Date'[Calendar Date] >= Start12MonthsAgo
                && 'Date'[Calendar Date] <= LastDatePreviousMonth
        )
    )
VAR SelectedMthEst = [Current Month Estimate]
RETURN
    VALUE (
        IF (
            ISBLANK ( SelectedSalesPrevMonths ),
            SelectedMthEst,
            SelectedSalesPrevMonths
        )
    )

The final measure is the one that I feel needs the most explaining, which I will attempt to do below, and I hope it makes sense to you the readers, as it does make sense to me!

  • In lines 2 & 3 is where I am finding creating a Variable LastDataDate which is the last or max Order Date.
    • The reason for this is often data is not always as fresh as it could be. Meaning that it is often a day behind or if some other process upwind has failed it could be a few days behind. This ensures that I am getting the max Order data from my dataset.
  • Lines 4 & 5 is where I am creating a variable Start12MonthsAgo that will be the First date of the month from 12 months ago, based on my Variable LastDataDate
    • Here I used the DAX DATE function to get the date, and based on my data this would return the following date based on the 09 Dec 2017
  • Lines 6 & 7 is where I am creating the variable LastDatePreviousMonth which will store the last date of the previous month, based on my Variable LastDataDate
    • Here I used the DAX EOMONTH function to return the date based on 09 Dec 2017
  • Lines 8 to 16 is where I am creating the variable SelectedSalesPrevMonths for the Sales for the previous months prior to the current month I am in.
    • As with this example I want to get all the sales using the two variables I created Start12MonthsAgo (11/01/2016) and LastDatePreviousMonth (11/30/2017)
    • As you can see below this is the virtual values which will be stored as part of this variable
  • Line 17 is where I am mapping the previous measure [Current Month Estimate] to the variable SelectedMthEst
    to make it easier for readability.
  • And finally, on lines 18 to 25 is where I am using all the above logic to dynamically decide which measure will be for the previous 12 months, excluding the current month. And then where the SelectedMthEst
    will go.
    • One line 19 I must define it has a VALUE which ensures that the measure returns it as a Number value and not a Text value.
    • Line 20, is where I use an IF Statement to determine what variable to display based on the conditions I specified in my variables.
    • Line 21, is where I am evaluating the ISBLANK based on my variable SelectedSalesPrevMonths
      • This is where it might become a bit confusing to some people, because virtually when I create the table there will be no value for the current month for the SelectedSalesPrevMonths measure because it stops at the end of Nov 2017.
      • In the table context where I have got dates going past Nov 2017, the measure SelectedSalesPrevMonths will have BLANK values.
    • Line 22, is where if the measure SelectedSalesPrevMonths is indeed BLANK, then use the measure SelectedMthEst
      which once again is for the current month.
    • And finally, in line 23, is where the IF condition is evaluated to be FALSE, then use the measure SelectedSalesPrevMonths
    • After which I close all the required DAX functions.
  • Hopefully this image below explains it better below, where I have visually shown where the SelectedSalesPrevMonths ends and where the SelectedMthEst starts

Measure in Action

Now that I have gone through and explained how it all works, it is best to see it in action.

  • Let’s suppose that the current date was the 11 Dec 2017, this would then show the entire sales as shown below, including the [Current Month Estimate] measure, where it currently is estimating a good month for Dec 2017
  • Whilst if it had to be later in the month say just before the break on 22nd December 2017 it would then reflect the following below. Which is now showing that potentially sales are down.
  • And as you can see above it shows quite a different picture to the previous one.

Conclusion

What I have demonstrated here, is how to use variables and multiple measures, so that to the end user it appears as one measure. Not only that, but it completes their exact requirement, and can give them a quick indication on how their sales are going as the month progresses.

As always, any comments or suggestions are most welcome in the comments below.

Reference creating the date table: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

Here is a link to the Power BI file: Substituting Measures.pbix

BI-RoundUp – Power BI (Developer Update – Balanced Scorecard – Deep dive at Data & BI Summit Ireland – Difinity Conference)

Here is the weekly BI-RoundUp, lots of great things happened in the past week!

Power BI – Developer Update

There is now a developer update for Power BI Developer, and there are a whole host of new updates for the Power BI Developers. I will give a brief overview of each section and if you want more details please click on the link further below.

Embed Capabilities

With the single visual embedding there now is the functionality to be able to embed a single visual into a page as shown below. Or it is possible to select multiple visuals and decide their size and position of each one

Now there is also the option to define the Custom layout sizing with regards to the Page Layout as well as the Visual layout.

There is now also a phased loading of embedded content where there is the option now to call it in the following order

  • Preload
  • Load metadata
  • Render Object

There is now more Tile Embedding options as shown below

We added support for more tile types that can be embedded. Here are the additional tile types:

  • Live page tiles
  • Custom visual tiles
  • R visual tiles
  • Live stream tiles
  • Image tiles
  • Video tiles
  • Web content tiles
  • Q&A tiles
  • Visio tiles

Automation and Life Cycle Management

There is the option now to Update a report, as with their example if you have deployed a report to 20 different organizations and you want to make a change, you can now leverage the Update report API. But this is something that I would test and read through the documentation as it does say that “an update operation changes the entire content of the report”

There is now also Row Level Security (RLS) for Tiles and Dashboards that have been embedded

New Capabilities in Azure

There is now the capability to use the Azure Resource Manager (ARM) with PowerShell to automatically manage Power BI Embedded Azure SKU’s (A)

As well as an updated version of the Custom Visuals API in which there is now Authentication with Azure Active Directory (AAD) and localize anything with the localization manager.

You can find all the details for the Power BI Developer Update here: Power BI Developer community January update

Power BI – Balanced Scorecard


This blog post goes into details on how people within Microsoft are utilizing Balanced Scorecards and KPIs to measure their success. It goes into details on how they measure their success and use Power BI in order to visually see how they are doing.

This is a great blog post if you are looking to implement scorecards and KPIs and is well worth the read.

You can find more details here: Balanced Scorecards in Power BI

Power BI – Deep Dive at Data & BI Summit in Ireland

If you are looking to go to the Data & BI summit in Ireland from 24 – 26 April 2018, be sure to attend the Deep Dive, which will be presented by Will Thompson, Amanda Cofsky and Chuck Sterling from Microsoft.

You can find more details about the Deep Dive here: Microsoft Deep Drive Track at the Data and BI Summit April 22-26

Power BI – Difinity Conference

I am honoured to be presenting my session at the Difinity conference on Power BI Row Level Security Made Easy

It is not too late to get a ticket, there are also some great pre-con sessions and I am almost spoilt for choice in terms of which one to select.

I am also looking forward to meeting up with fellow Power BI guru’s, as well as others from Microsoft.

If you want to find more information please find it here: Difinity 2018

Power BI – Losing Disk space when working with Power BI Desktop

I recently had an issue on a customer’s server, where I was developing Power BI Desktop reports. And the C: Drive was running out of disk space, which was attributed to my user profile.

Upon inspection I found that the TempSaves directory had a whole stack of temporary files saved in there. Even though I had previously selected to change the option in Power BI Desktop to delete files

I am pretty confident it has to do with the Auto recovery feature in Power BI Desktop.

If you are ever having any issues here is where you can go and delete the unwanted files that are no longer relevant.

Click on Start, then Run and put the following into the Run Command below.

%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\TempSaves

This should then open the location of the Temp Saves for Power BI Desktop Files.

I then selected all the files that I knew I no longer needed.

And then deleted them.

NOTE: I did keep the most recent file, which had todays date, which I did currently have open.

I then could get back roughly 10GB of disk space.

BI-RoundUp – Power BI (January 2018 Update – Power Apps Custom Visual)

Welcome to 2018, and I am sure that everyone is keen and ready to hit the ground running. I am really excited to see what this year will bring, both personally as well as with how things progress specifically in the Microsoft BI (Data and Analytics space). So here is my first BI Roundup for 2018

Power BI – January 2018 Update

It almost caught me by surprise that it was already time for another Power BI update, and here we are.

Reporting

The Show/Hide pages does not appear to be anything significant, but I have to admit that for me this is AWESOME. As I have been developing Power BI reports, I have often had to save multiple PBIX files, because I would be busy working on a new report, and someone would ask for a quick change. In order to get the change published I had to make a copy of the PBIX, then rename, change and upload.

Now I simply can hide the page in my existing PBIX file, and when read (or if not required) I can unhide the page.

Next is the ability to control the background colours for Cartesian and map controls. This has been available in other chart types, but not it is available in more charts.

Also in the past when using charts with labels that are rather long, they have often been truncated. Now with the latest update I can modify the maximum size in order to get more of the labels visible. As shown I can change the sizing and I can see more details on the label.


Along with this, under the same section in the charts is now the ability to be able to set the Inner Padding for Bar and Column charts. As shown below by changing the Inner Padding, the bar chart can start to almost look like a histogram. Likewise, I can also shrink the Inner padding to make the bars smaller than the defaults.

Showing the dates as a hierarchy is another update that will help when you do not have a date table in your model. I personally would suggest creating a date table, because this unlocks all the Time Intelligence measures within Power BI. But with that being said now with a Date Hierarchy, I can now select a single value from the hierarchy and it will only give me those values. If I select Month, it will then only show me the months.

When using the Relative Date Slicer, there now is the option to select an Anchor date. This is an awesome feature especially if you want to ensure that your starting date is from a specific period. As with the example in the video from Amanda where she references if you are doing quarterly reviews and you only want to see the previous quarter. You can use the anchor date to achieve this.

And finally, there is already an update for Q&A in the desktop, for the TOP N selection. I did blog about it this week, in terms of the hidden gems when using Q&A (Ask a Question (Q&A) Hidden Treasures in Power BI Desktop)

Analytics

In this section there was only one update with a new quick measure from Daniil Maslyuk, where he has created a quick measure for the Pearson correlation coefficient between two measures within a certain category.

Custom Visuals

Once again in this month’s updates there are a whole host of Custom Visuals, and I am going to give screenshots and the names of what they are. Especially the PowerApps Custom visual because it has its own entire blog post from the Power BI Team

Next is the TreeViz visual which is similar to a decomposition tree.

Funnel with Source by MAQ Software

Box and Whisker by MAQ Software

Agility Planning Matrix Light which reminds a bit of a pivot table

Image Grid

Gantt Chart by MAQ Software

Data Connectivity

There is now support for Azure Active Directory authentication for Azure SQL database and Data Warehouse connectors. Which is really great because some customers these days only have all their data in the cloud. As well as others being federated from their local active directory.

And finally, there is the option to change the display language not only for the application (which is the front end or where you create the reports), but also for the Model (Which is the query editor where you can mash the data up)

You can find all the blog post details here: Power BI Desktop January Feature Summary

Power BI – Power Apps Custom Visual

This is something that I know a lot of people have been waiting for. And now it is finally here. The capability to include Power Apps into my Power BI report.

This opens endless possibilities, as an example I now could enable write back from within my Power BI report, back to my source data. Which would then update the report. I could also potentially use Microsoft flow via Power Apps to distribute content or send emails directly from my Power BI report.

I will not go into too much detail, but rather wanted to highlight that the capability is now there.

You can read the fascinating blog post here: From Insights to Action with the Power Apps custom visual

Ask a Question (Q&A) Hidden Treasures in Power BI Desktop

The new Ask a Question (Q&A) in Power BI Desktop really does make it quick and easy to create new visuals.

Here is a link to the blog post from the Power BI Team: Q&A for report creation (preview)

I have been using Q&A in the Power BI Service for some time, and there are a few hidden treasures that you can now also use in Power BI Desktop. I will demonstrate a few of them that I know below.

I think the hidden gem is that the amazing people in the Power BI team have enabled Q&A to understand context so that you can not only ask questions as we would ask, but also it understands things like dates (Today, This Month), as well as for example highest, top, bottom etc.

So this can then be leveraged to easily create visuals that will always be in the context that they were created in.

Q&A for today

The first example, I created a card showing me the sessions for today 04 Jan 2018. The reason I wanted this, is because I am always interested in how many sessions I have for today. So as time moves, so will the card below, to always show me today, which is the context it was created in.

HOW COOL is that!

In order to confirm this, I validated it with the following screenshot below, showing what my sessions were for today 01/04/2018. As well as the DateTime where I have my PBIX file open.

And if I look on closer inspection at the Visual level filters, I can see where it has applied the filter automatically for me.

Q&A for current (this) month

A second example is where I want the total sessions for the current (this) month, so that as time moves on it will always show me the current month that I am in, again the context in which I created it. I did not script this at all, that it would add up to 1,000 exactly!

One thing to take note if, is that I chose “this month (Date > Date), which is from my Date table, and the Date column. This is because I have created a relationship from my Analytics table to the date table. This ensures that it is used within the right context.

Once again if I look at the Visual level filters it shows me how it created the measure in the card, as well as it shows that it will move with time.

Other hidden gems

As I found out from the documentation there is a whole host of hidden gems that you can use in context when asking a question.

Tips for asking questions in Power BI Q&A

At the end of this blog post are the current words and terminology that Q&A recognizes.

Differences between Q&A in the Power BI Service and Power BI Desktop

EDIT: 10 Jan 2018

Once again the Power BI team is incredibly quick at updating Power BI Desktop, and where I had previously said there were some differences between Q&A in the Power BI Service and Power BI Desktop, with the January 2018 release, it now appears to be identical.

So as shown below, if I was now searching for the date of my Max Sessions, this can easily be achieved with the following shown below.

 

 

 

 

 

 

 

 

 

 

Once again I am really impressed with Power BI, and I love working with the product!

It would be nice to have the ability to edit the Q&A as you can do on the Service. So I have created the following idea on the link below.

Vote here: Edit Q&A questions in Power BI Desktop

I hope that you have found this blog post useful and can start leveraging Q&A in Power BI Desktop, it really is a fantastic feature.

Power BI – Q&A Words and Terminology

If you are the owner of a dataset, add phrasings and synonyms to improve the Q&A results for your customers.

Aggregates: total, sum, amount, number, quantity, count, average, most, least, fewest, largest, smallest, highest, biggest, maximum, max, greatest, lowest, littlest, minimum, min

Articles: a, an, the

Blank and Boolean: blank, empty, null, prefixed with “non” or “non-“, empty string, empty text, true, t, false, f

Comparisons: vs, versus, compared to, compared with

Conjunctions: and, or, each of, with, versus, &, and, but, nor, along with, in addition to

Contractions: Q&A recognizes almost all contractions, try it out. Here are a few examples: didn’t, haven’t, he’d, he’s, isn’t, it’s, she’ll, they’d, weren’t, where’ll, who’s, won’t, wouldn’t.

Dates: Power BI recognizes most date terms (day, week, month, year, quarter, decade, etc…) and dates written in many different formats (see below). Power BI also recognizes the following keywords: MonthName, Days 1-31, decade.

Examples: January 3rd of 1995, January 3rd 1995, jan 03 1995, 3 Jan 1995, the 3rd of January, January 1995, 1995 January, 1995-01, 01/1995, names of months.

Relative dates: today, right now, current time, yesterday, tomorrow, the current, next, the coming, last, previous, ago, before now, sooner than, after, later than, from, at, on, from now, after now, in the future, past, last, previous, within, in, over, N days ago, N days from now, next, once, twice.

Example: count of orders in the past 6 days.

Equality (Range): in, equal to, =, after, is more than, in, between, before

Examples: Order year is before 2012? Price equals between 10 and 20? Is the age of John greater than 40? Total sales in 200-300?

Equality (Value): is, equal, equal to, in, of, for, within, is in, is on

Examples: Which products are green? Order date equals 2012. Is the age of John 40? Total sales that is not equal to 200? Order date of 1/1/2016. 10 in price? Green for color? 10 in price?

Names: If a column in the dataset contains the phrase “name” (e.g., EmployeeName), Q&A understands the values in that column are names and you can ask questions like “which employees are named robert.”

Pronouns: he, him, himself, his, she, herself, her, hers, it, itself, its, they, their, them, themselves, theirs, this, these, that, those

Query commands: sorted, sort by, direction, group, group by, by, show, list, display, give me, name, just, only, arrange, rank, compare, to, with, against, alphabetically, ascending, descending, order

Range: greater, more, larger, above, over, >, less, smaller, fewer, below, under, <, at least, no less than, >=, at most, no more than, <=, in, between, in the range of, from, later, earlier, sooner, after, on, at, later than, after, since, starting with, starting from, ending with

Times: am, pm, o’clock, noon, midnight, hour, minute, second, hh:mm:ss

Examples: 10 pm, 10:35 pm, 10:35:15 pm, 10 oclock, noon, midnight, hour, minute, second.

Top N (order, ranking): top, bottom, highest, lowest, first, last, next, earliest, newest, oldest, latest, most recent, next

Visual types: all visual types native to Power BI. If it’s an option in the Visualizations pane, then you can include it in your question. The exception to this is custom visuals that you’ve manually added to the Visualization pane.

Example: show districts by month and sales total as bar chart

Wh (relationship, qualified): when, where, which, who, whom, how many, how much, how many times, how often, how frequently, amount, number, quantity, how long, what

 

Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

  • I would first get the yellow car asking for permissions to run the native database query.
  • Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • Once I click Run it would then run the query.
  • So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

  • Click on File and then Options and settings and then Options
  • Then under GLOBAL I clicked on Security
  • Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
    • And there is a tick
      next to “Require user approval for new native database queries
    • I removed the tick.
  • Then click
    Ok.

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.

Conclusion

I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

BI-RoundUp – Power BI (December Desktop Update – Larger Power BI Premium PBIX – Power BI Bookmarking Contest – Premium Capacity WhitePaper)

Even though it is almost the holiday season, the wonderful people from the Power BI team have given myself (and hopefully you also) some early birthday presents. So please find all the updates below.

Power BI – December Desktop Update

There was an incredible number of updates in the December edition of Power BI Desktop, so please find below all the details.

Reporting

The first and biggest thing in the latest update was now the capability to be able to Ask a Question (Q&A) within Power BI Desktop, which relates to asking a question on a report page. Previously this could only be done in the Power BI Service on a dashboard.

I have used Ask a question (Q&A) in the Power BI Service previously and it works really well. But with this now being in Power BI Desktop, it enables business users to quickly create the visualizations that they are looking for. And then if need be they can customize them afterwards.

I also think that this will go a long way to facilitate self-service report creation, because it now means they can just ask a question and get a visual response.

Next is updates to Bookmarking:

  • The bookmark will now save your cross-highlighted state when you create your bookmark.
  • INSERT GIF HERE
  • Along with this there is more flexibility with regards to what is bookmarked.
    • Data – This will apply properties relating to your data such as filters and slicers
    • Display – This lets you decide if you want to include Visual Properties of the display item
    • Current Page – This lets you apply the bookmark to the current page only, if deselected it will not apply to the current page you are in.
    • All Visuals – This means that the bookmark will bookmark all the visuals on the entire page.
    • Selected Visuals – This will only apply the bookmark to the selected visuals, which I would have selected before applying the bookmark.

Next is the Field Properties Pane and Field Properties Description, which now allows you to add in a description for your Field Names. This is great to document or make a note within your Power BI Desktop model. Especially if someone else is going to come along and edit the Power BI Desktop PBIX, they can now find additional details within the file.

There is now the capability to be able to create a scatter visual from the x & y-axis groupings. What this means is that you no longer need to add in a category to see the details. The only currently caveat is, is that you have to use a Numeric column it currently does not work on measures. But I am certain that this will change in the near future.

If you use maps, and have had a lot of data points in the past, the new algorithm has also been applied to maps. This is great because where they are dense, the human eye will not be able to see any difference. But more importantly it will show the outliers, which are often very important.

(I know it is really wide, but that is how far I had to drag it across in Power BI Desktop in order to get the entire name!)

Along with this, there are now responsive slicers, which is an update to the existing responsive visuals. This makes it easier to use especially when on touch screen’s or mobile phones.

There are a whole host of Custom Visuals, some I have seen before, but some really cool new ones.

  • Card Visual
  • Table Heatmap
  • Data Image
  • Power Matrix KPI
  • Text Filter
  • ChartAccent – Line Chart
  • ChartAccent – Bar Chart

Analytics

In the analytics section there is now is the ability to drill filters to other visuals within your Power BI Desktop report.

What this means if you drill down in a visual it will apply the same filter to all the other visuals in your report. This is really fantastic, because I know personally that I often want to see the context of what I am drilling down to and how it relates with my other visuals.

Data Connectivity

Please find below new data connectors, as well as some updates to existing data connectors.

  • Adobe Analytics Connector
  • HDInsight Interactive Query connector
  • Data.World connector
  • SAP BW connector improvements – Ability to be able to connect to either the SAP Business Warehouse Application or Message Server.
    • As well as currently this improvement is only available in Power BI Desktop, and will be coming to the On-Premise Data Gateway soon.
  • IBM Netezza connector is now generally available

There is a lot of content to go through, so if you would like to watch the video or find more details here is the link to the blog post: Power BI Desktop December Feature Summary

Power BI – Larger PBIX sizes for Power BI Premium

As you can see above, available now in Power BI Premium, you can now upload PBIX files that are up to 10GB in size. I am sure that this is a great welcome for customers who have had larger PBIX files.

You can find the blog post details here: Announcing Power BI Premium support for larger datasets

Power BI – Bookmarking Contest

There is a Power BI Bookmarking contest where you could not only win some awesome Power BI Swag, but also be seen by the Power BI community as to how awesome your Bookmarking capabilities are.

More details can be found for the competition here: Bookmarking Contest: Dec. 12th – Dec. 27th

Power BI – Premium Capacity White Paper

Please find the link below to the newly released Power BI Premium White Paper

Power BI Premium White Paper