Press "Enter" to skip to content

Category: Power BI

Adding Public Holidays To A Date Dimension

Published 2017-09-18 by Kevin Feasel

Reza 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

Power BI Drillthrough

Published 2017-09-12 by Kevin Feasel

Reid Havens shows how to use the new drillthrough functionality in Power BI:

Report Drill Through enables users to create a report page, filtered to a single entity (E.g. Customer, Employee, Store, Product). Reports often have a summary landing page where there might be a lot of information, but not much detail about a specific item. Traditionally in Power BI we’d create a separate reporting page for further detail breakouts on an entity. However, that would require it’s own set of SLICERS that you would have to re-select if you wanted it to mirror the reporting page you were coming from…that’s too many clicks!

Selecting an item in Drill Through in a table from one reporting page will take you to another page, FILTERED to the entity you selected!This feature essentially let’s us create detail sub-pages that are linked to whatever primary reporting page the report uses. Features like this have been available for YEARS in Excel using linked cells, I’m super happy we finally have this as a feature in Power BI Desktop. It only takes a few steps to setup, but I’ll leave the instructions for that over at the Power BI September release page. With that said, let me run you quickly through how this looks like in a sample report.

Click through for an example, showing how useful drillthrough can be.

Comments closed

Fiscal Year Columns In A Power BI Date Dimension

Published 2017-09-11 by Kevin Feasel

Reza Rad takes a date dimension in Power BI and adds fiscal year details:

As you can see in the image above; June 2017 considered as fiscal year 2017. However, July 2017 is part of fiscal year 2018. So the simple logic can be like this:

if (calendar month >= fiscal year start)

then fiscal year = calendar year

else fiscal year = calendar year + 1

This code is pseudo code. don’t write that exactly in M! Let’s now implement it in M;

If you have to deal with multiple fiscal years (e.g., state and federal government fiscal years), the process is the same, only repeated.

Comments closed

DAX Is Still Important

Published 2017-09-11 by Kevin Feasel

Reid Havens explains why he teaches DAX in his Power BI courses:

The Check Formula button is an easily overlooked feature. However, before I hit ok and save my DAX Measure I ALWAYS press this button first! But what exactly does this button do? Well it’s checking your DAX syntax and making sure everything is written correctly. Now you COULD simply hit OK after writing your DAX and see if it errors, this is true. However when doing that your data model is actually attempting to calculate the DAX measure in the background as well. Not a big deal with a few thousand rows, but if you’re working with a model that has millions of rows then that could take a long time for it to calculate, and then error!

The smart thing to do is to check your DAX syntax using the Check Formula button BEFORE hitting ok. Checking your DAX syntax doesn’t run your calculation and returns a rewarding No errors in formula output if everything was written correctly. Such a simple thing that can save you SO MUCH TIME! I highly recommend as a best practice to always use this before hitting ok and saving your measures, you’ll thank me later.

It makes for interesting reading.

Comments closed

Chart Style Controls

Published 2017-09-11 by Kevin Feasel

Wolfgang Strasser shows off a new feature in Power BI:

The theme documentation provides a list of available visual names, cardNames and property names.

At this point some further explanation is needed for the hierarchy within the theme definition:

  • visualName corresponds to available PBI visuals like treeMap, card, columnChart,…

  • styleName (as of today I am not sure whereto this corresponds to PBI Desktop language.. :-)) maybe someone can further explain this to me

  • cardName corresponds to the formatting card/option within Power BI Desktop. Attention here: the name in the theme JSON file is defined different than the User Interface name + do not forget case-sensitivity! (i.e.  general => General; categoryAxis => X-Axis, valueAxis => Y-Axis, ..). See the documentation for the rest of the mapping.

This is good news if it makes it easier for developers to write CVD-friendly reports.

Comments closed

Sentiment Analysis In Power BI

Published 2017-09-07 by Kevin Feasel

Chris Webb has a new Power BI custom data connector:

I’m pleased to announce that I’ve published my first Power BI custom data connector on GitHub here:

https://github.com/cwebbbi/PowerBITextAnalytics

Basically, it acts as a wrapper for the Microsoft Cognitive Services Text Analytics API and  makes it extremely easy to do language detection, sentiment analysis and to extract key phrases from text when you are loading data into Power BI.

Read the whole thing, as Chris has a great demo of it.

Comments closed

Drillthrough In Power BI Reports

Published 2017-09-06 by Kevin Feasel

Dustin Ryan shows off drillthrough actions in Power BI:

At some point this weekend, the Microsoft Power BI folks turned on the drillthrough feature in the Power BI service. This is the same drillthrough feature that demonstrated during day 1 of the Microsoft Data Insights Summit, which you can read about here. So I thought it’d be good to quickly walk through how you can set up the drill through action.

First, navigate to a report that you’ve already published to the Power BI service. This report will become the target of your drill-through action. Start editing the report and you’ll notice that on the right side there is a Drillthrough Filters section.

Read on for more.  At the moment, this is only available within the Power BI service, not in Desktop.

Comments closed

Two Mobile Reporting Stories On The Microsoft Stack

Published 2017-09-05 by Kevin Feasel

Paul Turley talks about the two competing mobile stories on the Microsoft stack, namely SSRS with DataZen and Power BI:

This is the story of two products – or rather one product that is now a service and another product that is now a component of another product.  A few years ago, Microsoft began to formulate a mobile usability story among many fragmented tools.  They had a really good reporting product: SSRS, and they had a pretty good self-service BI capability offered as a bunch of Excel add-ins; namely: Power Pivot, Power Query and Power View – but it didn’t do mobile.  They bought Datazen which was a decent mobile reporting and dashboard tool, designed primarily for IT developers and semi-tech-savvy business pros to quickly create mobile dashboards using traditional data sources.  Datazen wasn’t really a self-service BI tool and wasn’t really designed to work with BI data in the true sense.  It was a good power user report tool but was young and needed to be refined and matured as a product.  Datazen became “Reporting Services Mobile Reports” and was integrated into the SSRS platform as a separate reporting experience with a separate design tool, optimized exclusively for use on mobile devices using platform-specific mobile phone and tablet apps.  Since initial roll-out, product development stalled and has not changed at all since it was released with SQL Server 2016 Enterprise Edition.

Paul gives us his current advice, as well as a hint at where things could be going.

Comments closed