A Fine Slice Of SQL Server
Matt Allington shows how you can create a combination slicer and histogram in Power BI:
I was doing some online shopping last week and saw a price slicer with a histogram to show the concentration of price points. I thought it was pretty cool, so decided to see if I could build this in Power BI. I came up with a solution that works, and in this article I show you how you can do it yourself.
Click through for instructions and a video.
Comments closedPaul Turley continues a series on drillthrough. First, from a paginated report into Power BI:
In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.
Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.
After that, Paul posted a follow-up on the wherefore:
I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle. As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.
It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.
It’s good to see interoperability between these two tools.
Comments closedI have had seen a few questions in the Power BI Community, as well as from other people asking if it is possible to connect directly from Excel to a Power BI Premium dataset from Excel. Without having to log into the Power BI Service or download any .ODC files.
I am going to show you how to do this below.
Read on for a screenshot-fueled demonstration.
Comments closedPaul Turley shows how you can drill through from a Power BI dashboard into an SSRS report:
This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.
The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).
These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.
You can’t get the files just yet, but you can see what Paul does to get this working.
Comments closedImke Feldmann has a lengthy Power Query script to help debug issues with DAX variables:
When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.
Please note, that currently only comma separated DAX code is supported.
Click through for a demo as well as a video.
Comments closedWhile working on projects, It is always my goal to keep query folding active as long as possible. Simply because it improves my dataset refresh timings and reduces the load executed on Power BI side.
While working on a project last week, I found something interesting in default Power BI functionality which breaks query folding, but can be avoided by slightly changing the Power Query code yourself or picking another option which results in exactly the same. So, this week a short blog post to make you aware of things which might brake query folding.
Click through for two scenarios, one where a transformation can break query folding and one where query folding is still fine.
Comments closedThe OAC environment that Opal gave me access possessed an example schema/data based on an Audio-Video store revenue for multiple years.
I’d never worked with the OAC before, but I was quickly able to find five methods to connect Power BI to it, either to import or to direct connect to the data:
1. Data Direct makes a Rest API to connect to the Oracle Cloud.
2. Use the web interface with Power BI and “scrape the website”, allowing PBI to build the table from example.
3. Connect with an ODBC driver
4. Via the OAC interface, export to Excel and then import into Power BI
5. Via the OAC interface, export to a table and then import into Power BI as a CSV file.
So, uh, yeah, you can do it. At least five ways.
Comments closedChris Webb has bad news for people with tables holding 2 billion-plus rows in Tabular format:
What’s more, the error will always occur even if you apply a filter to the table that returns less than two billion rows. The same problem occurs with some other functions, such as Percentile(), but it’s worth pointing out that the vast majority of DAX functions work as normal with tables with more than two billion rows – for example, in the pbix file used here the Sum() and CountRows() functions not only work fine but return instantly.
I haven’t seen that many Power BI examples with 2 billion or more rows in a table, but it can be an impediment when trying to use Analysis Services Tabular in cases with enormous amounts of data.
Comments closedKasper de Jonge shows us a few new tricks with Power BI:
As of this month Power BI finally supports filtering slicers down to only show rows that have fact data. Before the only thing you could do to achieve this was some workaround like: I described here where you filter down the dimension using a calc table. The other approach was to use Bi Directional cross filtering which would filter down the dimension table appropriately. This leads to performance issues though.
Now you can use a measure to filter down the slicer.
Click through for an example as well as a few other tricks you can do as a result.
Comments closedMatthew Roche shows off the “column from examples” feature in Power Query:
Here’s the quick overview:
1. In the Power Query editor in Power BI Desktop, choose “Column from Examples” from the “Add Column” tab.
2. Enter the values that the new column should have for rows that are already in your data set.
3. Review the values that Power Query is suggesting for the other rows, and when they are all correct, choose OK, and then say “Ooooooohhhhh” when Power Query does all the work for you.
It’s a little hard to see from Matthew’s image but this is a great feature when you have a regular pattern but don’t want to put together a regular expression yourself.
Comments closed