A Fine Slice Of SQL Server
Devin Knight continues his Power BI custom visuals series:
In this module you will learn how to use the HTML Viewer. The HTML Viewer allows you to display the results of HTML code within your Power BI reports.
It does what it says on the label, and that’s good enough for me.
Comments closedDevin Knight continues his Power BI custom visuals series:
In this module you will learn how to use the Timeline Storyteller. The Timeline Storyteller is a great way to tell a story about your data. It gives you the ability to create multiple representations of your data and then pull them together by creating multiple scenes.
This is a flashy visual and I think Devin’s set is an excellent example of where you might want to use it.
Comments closedMatt Allington shows us how to use variables in DAX:
Variables in DAX is a relatively new feature and is available in
- Power BI Desktop
- Excel 2016
- SSAS Tabular 2016
Variables are not available in Excel 2013 or Excel 2010.
Click through to see how to assign and use variables. It’s interesting to see how they’re local to a measure, so at this point at least, you can’t share variables between measures. Given what DAX is supposed to be, that’s probably the right choice.
Comments closedReza Rad explains what ribbon charts are and why it’s good that they’re now available in Power BI:
Ribbon Chart shows bigger value in each column at the top, then the next value comes after. Look at the sales amount value for female and male in 2005 and 2006. In 2005, Female (Black) had more sales than Male. However, in 2006, Male (Green) generated more revenue than the female, so it is on the top for the 2006 column.
The main benefit of the ribbon chart is in this re-ordering, so it’s easier to tell which categories are largest in a specific time period.
Comments closedJulie Smith has found the only way to keep up with Power BI changes—using a Power BI report:
Microsoft makes major improvements via monthly and sometimes weekly releases to Power BI. In my time working on Power BI projects at Innovative Architects, I have found that the only way to stay on top of the frenetic pace of Power BI’s improvements is to closely follow its blog and other social media feeds.
This page is a Power BI report (how meta right?) built off of the content from Microsoft’s Power BI blog. I have set it up to run daily and refresh. Please let me know in the comments if you feel that the data has become stale. As part of building this report, I have added some curated slicers–things like PBI Service, PBI Desktop, Gateways, Connectors– and blogging content tags such as Contest or Webinar. Keep in mind that this is my best effort and there is no guarantee on the accuracy of my tagging. I provide this as my gift to you “as is” with no expressed or implied warranty.
Click through to see the report.
Comments closedIn the last two screenshots the ABC123 icon in the column headers show that they are set to use the Any data type; the columns returned by calling the function have lost their data types.
The key to solving this problem is using the optional fourth parameter of the Table.AddColumn() function, which allows you to set a data type for the column that function adds to a table. Altering the Invoked Custom Function step of the previous query to do this, setting the new column to be a table type like so:
Worth reading in its entirety.
Comments closedMeagan Longoria has an after-action report of a proof of concept using Power BI Report Server:
In addition to managing to versions of Power BI Desktop, I also found myself mentally managing two sets of features. I was constantly asking myself “Can I do that in Power BI Report Server?”. Some of that is because PBI Desktop for Report Server is on a quarterly release cycle rather than monthly, so I had to remember if a feature I wanted to use was new (or in preview) and therefore not available in this version. The other part is trying to remember what you can and cannot do with a Live Connection. For example, you can make report measures, but you can’t use ad hoc grouping and binning.
We had several scenarios where users wanted to be able to group fields in multiple ways that changed somewhat frequently. Since we couldn’t use grouping and binning in Power BI Desktop to accomplish this, we set up an Excel data source in the SSAS Tabular model, and allowed users to change the groups there and refresh the Tabular model when finished. This could get rather unwieldy if you had lots of users who needed this kind of flexibility.
Ultimately, the customer considered it a success. Read on for more details.
Comments closedJohn White compares and contrasts SQL Server Reporting Services versus Power BI Report Server:
Power BI Report Server (PBIRS) was first introduced in May 2017. Based on SQL Server Reporting Services (SSRS), it brings the ability to work with Power BI reports completely on premises in addition to all the other capabilities of SSRS. Given this, it would be reasonable to conclude that PBIRS was the next version of, or a replacement for SSRS, but that is not the case. I have heard people state that SSRS is “going away”, but this is simply not the case. SSRS is still a core part of the Microsoft BI stack. So, what are the differences between the two platforms? The differences boil down to features, licensing, and update cadence.
If you’re in the BI/report writing space, you will want to read the whole thing.
Comments closedLeila Etaati shows how to build a polar chart in Power BI using an R component:
I just add a layer to the above furmula “coord_polar()” this function also has been used for creating pie charts. it gets the “theta” variable, in below example I put theta=y axis, so we have below charts
Normally I don’t much like this type of polar chart, though I’m a big fan of radar charts, which follow a similar concept.
Comments closedReza Rad continues his series on Power BI date dimensions:
To get public holidays live, you first need an API that is giving you up-to-date information. There are some web pages that has the list of public holidays. I have already explained in another blog post how to use a web page and query public holidays from there. That method uses custom functions as well, here you can read about that.
The method of reading data from a web page has an issue already; Web.Page function from Power Query is used to pull data from that page, and this function needs a gateway configuration to work. There is another function Xml.Document that can work even without the gateway. So because of this reason, we’ll use Xml.Document and get data from an API that provides the result set as XML.
WebCal.fi is a great free website with calendars for 36 countries which I do recommend for this example. This website, provides the calendars through XML format. There are other websites that give you the calendar details through a paid subscription. However, this website is a great free one which can be used for this example. WebCal.fi is created by User Point Inc.
This was an interesting approach to the problem, one I did not expect when first reading the article. I figured it’d be some sort of date calculation script.
Comments closed