A Fine Slice Of SQL Server
Melissa Coates announces some new documentation:
I’m really pleased to announce that new Power BI Migration documentation that I authored is published on the Microsoft Docs site. Although it was written from the perspective of migrating to Power BI from another platform, there’s no doubt that a lot of the content applies to a standard Power BI implementation project.
Click through to learn more and follow Melissa’s link to get the whole document.
Comments closedFred Kaffenberger poses a question:
If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.
Read on for a verification technique.
Comments closedSoheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:
This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy.
If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.
Comments closedMatt Allington wants to solve a problem:
Using a switch measure to toggle results is a mature and common technique used in Power BI and Power Pivot for Excel. For example, a switch measure can be used to toggle what appears on a chart so that the end user can easily switch the data being visualised (see image below).
This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart. I blogged about this in 2014 here.
The switch measure has a drawback, though: you can only have one number format, so Matt has a new solution using calculation groups.
Comments closedGilbert Quevauvilliers shows how we can display the last several days as column headers:
I find I do love a good challenge and I find that I get them frequently. I also enjoy sharing what I have learnt with others.
In this blog post I am going to show you how I found a way to create Dynamic Date Column Headers for Tables or Matrixes in Power BI.
Below is what it looks like once completed. What I did was to create a matrix with the last 5 Days (Showing each date for the last 5 days) and then along with this an MTD calculation which shows not only the header of MTD but it includes the current months values.
This is an interesting one for sure. Read on to see how Gilbert solved the problem.
Comments closedDavid Eldersveld has some tips to make it easier to read your dashboards:
Power BI font selection is currently limited to about two dozen choices. These include a mix of serif versus sans serif, monospaced versus not monospaced, bold versus light, and more. While I do not have a definitive list of specific fonts–and I would love additional input from readers who live with dyslexia–there are some preferred font choices as well as choices to avoid when designing Power BI reports.
Click through for good recommendations regardless of whether any of your dashboard viewers are dyslexic.
Comments closedMeagan Longoria has a new Power BI report for us:
Color contrast (as calculated in the WCAG 2.1 success criteria) is dependent on luminance. Luminance is the relative brightness of any point in a color space, normalized to 0 for darkest black and 1 for lightest white. In order to calculate color contrast you must first get the luminance of each color.
As an example, I have colors #F3F2F1 and #007E97. In this hex notation, often explained as #RRGGBB, the first two digits represent red, the second two digits are green, and the last two digits are blue. Each two digits is a value that represents the decimal numbers 0 to 255 in hexadecimal notation. The same red, green, and blue values can be represented in decimal notation as integers, and this is what is used to calculate luminance. #F3F2F1 is RGB(243, 242, 241), and #007E97 is RGB(0,126,151).
Read the whole thing, check out the report, and test those color contrast values.
Comments closedChris Webb lays out map routes:
In last week’s post I described the new Power Query M functions for working with Well Known Text objects, and in a post a few weeks ago I showed how you can use the Icon Map custom visual to display Well Known Text data. In this post I’ll show you how you can put all this together to do something really useful: display routes on a map in a Power BI report.
First of all you’ll need to download the latest version of the Icon Map custom visual here, because at the time of writing the version in AppSource doesn’t have the WKT functionality needed. Second, you’ll need a data source with a table of latitudes and longitudes that represent points on a route that you want to plot. I’m going to use the Azure Maps Get Route Directions API as my data source; to keep things simple I’m going to use the Shared Key authentication method for this API, but I’ll explain below why this can be a problem in the real world. To follow the example you’ll need to create an Azure Maps Account in the Azure Portal so you can get your own key.
Read on for an interesting demo.
Comments closedDavid Eldersveld controls the horizontal; David Eldersveld controls the vertical:
The default Power BI report layout uses a landscape orientation. While it’s not common to see report pages that use portrait mode, Prathy Kamasani (blog/twitter) has a notable example in Power BI here. Others have tried it as well. It’s used occasionally in other data communities or data-driven journalism. Overall, if you want to guide someone through your analysis by having them scroll like a web page, it may be an option to try.
So what happens if I create a report with a vertical layout in Power BI?
Read on for an example as well as some pros and cons of the idea.
Comments closedPhil Seamark shows us how to write queries against Power BI using Visual Studio Code:
It’s helpful to understand there are two main client libraries for Analysis Services. A client library is what you can add to any new Visual Studio Code Project to provide objects, methods and functions relevant for the tool you are building.
Make sure you download the NetCore (.Net Core) versions of these libraries when working with Visual Studio Code. There are .Net Framework versions of these libraries that are more suited to use with the full Visual Studio product.
Read on for links to those libraries and a thorough demonstration.
Comments closed