A Fine Slice Of SQL Server
I 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 closedSome folks, either to avoid the need to buy a Microsoft Power BI license or in trying to embed Microsoft Power BI content in an On-Premises site like Microsoft SharePoint 2013, published their content using this function.
The risk is that if the content is on a page that gets indexed by a major search engine, like Google, the embed code will likely live in Google’s index forever. Then anyone can search for your data.
Read on to see the right way to do this. Treb also notes that there are good use cases for Publish to Web; you just have to make sure yours is one of them.
Comments closedMatt Allington takes us through a new feature in Power BI:
Now back to the point of this post. As of June 2019, it is possible to pre-filter slicers as well. It may seem weird, but this previously wasn’t possible – it seemed weird to me, anyway. This is now fixed and it is possible to use the side filter pane in the same way as other visuals. I can think of quite a few useful scenarios, including:
– Hiding the dreaded (Blank) in a slicer. [Actually, please don’t do that, but instead fix your data model].
– Filtering out items not relevant (eg category managers may only want to see their own products)
– Hiding items with no sales
Read on for a few examples of how to use this.
Comments closed