Category: Power BI

Dustin Ryan talks about another batch of Power BI updates:

1. Conditional Formatting on Tables

This one is awesome and has been a feature that we’ve all been waiting on for a while. We now have the ability to apply conditional formatting on a field based on a numeric value, as seen in the screenshot below.

Conditional formatting is a great addition.

Chris Webb shows how to create a report with with a single slicer that allows the ability to show data for the current day, week, month, and year:

The way to achieve this is not all that different from the calculated column approach, but it requires a separate table to model the many-to-many relationship between all the required relative period selections and the dates in them, as well as the use of bidirectional cross-filtering between tables (which I blogged about here). The data model I used for this report looks like this

Be sure to read the comments to make sure you don’t get into a scenario in which a user can select multiple periods and get duplicated data.

Avi Singh walks through a usability scenario in Power Pivot and Power BI:

Here is how we can go about combining the Products and the newly created SubCategory table for a more usable and elegant solution. I have highlighted the key steps, you can examine the solution file to go step by step in the Power BI Query.
Note: Don’t be scared of the M code, 99% of these steps were generated using the Query ribbon – I have included those screenshots as well.

When developing BI models, it’s important to keep things as simple as possible.  Your desired end user likely does not have the necessary skill level to wade through normalized table designs, so make it easy for them to get their jobs done.

Ginger Grant is good with using custom visualizations in Power BI:

Now since Power BI Custom Visualizations are not provided by Microsoft, they feel compelled to give you a warning message letting users know this. Here is the message box you get in Power BI Desktop when using a custom visualization. Notice that I clicked on the check box next to the text Don’t show this dialog again. As Words mean things, checking this box means the warning message never appears again. When you import the visualization into Power BI, no warning messages. Now I can use and propose custom visualizations to clients because they really are neat, and now they contain no warnings. Thanks so much to the Power BI Product team for fixing this major issue.

This is good news.

Chris Webb explains “let” expressions  in M:

In the M language a let expression consists of two sections. After the let comes a list of variables, each of which has a name and an expression associated with it. In the previous example there are three variables: step1, step2 and step3. Variables can refer to other variables; here, step3 refers to both step1 and step2. Variables can be used to store values of any type: numbers, text, dates, or even more complex types like records, lists or tables; here, all three variables return numbers. The Query Editor is usually clever enough to display these variables as steps in your query and so displays then in the Applied Steps pane on the right-hand side of the screen

It’s a look at one of the fundamentals of an interesting language.

Reza Rad shows a few ways to deal with date/time issues related to Power BI being in the cloud:

Power BI is a cloud service, and that means Power BI files are hosted somewhere. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. So If you use DAX functions such as TODAY() or NOW() you will not get your local date/time, You will fetch server’s date/time. In this blog post I’ll explain methods of solving this issue, so you could use Power BI to resolve your specific time zone’s date and time. If you want to learn more about Power BI read Power BI online book; Power BI from Rookie to Rock Star.

This is your daily reminder that “the cloud” is just somebody else’s machine.

Reza Rad has part 3 of his event date and time scheduler up:

This table has three columns: Date, Time, and Duration. I separated the date and time for simplicity of this example. Date to be formatted as YYYYMMDD, and Time as HHMM, and duration as an integer value illustrating hours.

Configuration above means the event starts at 9^th of May 2016, at 1:00 pm New Zealand time (this is what my local time is), with duration of 3 hours. I named this table as InputData.

This wraps up his series on Power Query for non-BI developers.

Chris Webb shows how to take a parameterized query in Power BI and create an M function from it:

All of these examples involve writing M code manually. The big change in the latest version of Power BI Desktop is that you can do the same thing using just the UI.

Let’s take the classic example of combining data from multiple Excel workbooks and update it to show how things work now.

Say you have a folder containing three Excel workbooks containing sales data for January, February and March and you want to load data from all three into a single table into Power BI. The first thing to do is to create a new parameter in Power BI Desktop that returns the filename, including path, of one of the Excel files. Call it ExcelFilePath and configure it as shown here:

This is pretty cool.