Press "Enter" to skip to content

Category: Power BI

Displaying Human-Readable Month Sets With DAX

Published 2018-12-28 by Kevin Feasel

Alberto Ferrari wants to show sets of contiguous months using DAX:

Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:

I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:

I removed the image, but to get the gist (and get you to click through to see it in its beauty), it reads “January, March-April, August-December”

Click through for Alberto’s quick-and-dirty solution and then Chris Webb’s improvement.

Comments closed

Power BI Tab Order

Published 2018-12-27 by Kevin Feasel

Meagan Longoria shows us how to set the tab order for our Power BI elements:

Tab order is the order in which users interact with the items on a page using the keyboard. Generally, we want tab order to be predictable and to closely match the visual order on the page (unless there is a good reason to deviate). If you press the tab key in a Power BI report, you might be surprised at the seemingly random order in which you move from visual to visual on the page. The default order is the order in which the visuals were placed on the page in Power BI Desktop, or the last modified order in PowerBI.com if you have edited your report there.

I had not actually tried to tab through a dashboard in Power BI, so this is news to me.

Comments closed

Getting The Latest File With Power Query

Published 2018-12-21 by Kevin Feasel

Matt Allington shows us one technique to get the latest version of a file using Power Query:

This pattern is common if your new file contains a superset of all the data.  It could be a transactional file that grows in length each time or it could be a dimension/lookup table (such as Customers) that can change slowly over time, and you always want to see the latest version.  My advice to all my Power Query students is “zero touch the file”.  In other words, your objective should always be to have the absolute minimum amount of interaction with the source files possible and push all the work into Power Query.  This will minimise the amount of work/rework you have to do in the future.  Thinking about the use case here “load the latest version of a file”, the question becomes “how can I make this zero touch”?  There are a few issues to consider including naming/renaming of the file and also archiving old copies of the file.  This doesn’t sound like zero touch to me.

Read the whole thing and check out the video.

Comments closed

Conditional Formatting In Power BI

Published 2018-12-21 by Kevin Feasel

Teo Lachev walks us through conditional formatting in Power BI:

If you have used SSRS, you know that paginated reports are very customizable, and you can make almost any property expression-based. Power BI is yet to deliver expression-based properties to change settings based on runtime condition, such as to change the font style based on the actual value or user selection. Currently, there are two places where you can use a DAX measure for expression-based formatting of colors:

Click through to see those two places. I think I’d like to see conditional formatting be a bit easier than that.

Comments closed

Calculating Net Present Value And Internal Rate Of Return With DAX

Published 2018-12-21 by Kevin Feasel

Annie Xu walks us through a couple of financial calculations and how to implement them in DAX:

The Excel XNPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals. Calculate net present value for irregular cash flows. Net present value. =XNPV (rate, values, dates)

The Excek XIRR(Internal Rate of Return) is the discount rate which sets the Net Present Value (XNPV) of all future cash flow of an investment to zero.  If the NPV of an investment is zero it doesn’t mean it’s a good or bad investment, it just means you will earn the IRR (discount rate) as your rate of return. =XIRR(values,dates,guess)

Click through to see how to do this in DAX, especially if your data is not in exactly the right format.

Comments closed

Using Hidden Slicers To Control Visible Power BI Slicers

Published 2018-12-19 by Kevin Feasel

Prathy Kamasani has an interesting problem to solve:

In my current project, one of the user requirement was to have a filter on the Year Slicer. To explain in detail, we have various measures to show metrics for current and earlier measures. For example 2016,2017 and 2018. In 2016, we always have blank values for Last Year metrics, having empty values don’t tell the story well. So to tell the story, we need to pull three years worth of data but display only two years in the Slicer. The easiest way to handle this situation would have had a visual level filter on the Year slicer.
Power BI Slicers doesn’t support Visual Slicers. However, with the help of Selection Pane and Sync slicers, I did a quick workaround.

Read on to see how to use hidden slicers to  control what’s displayed to the user in visible slicers.

Comments closed

Executing Python Code In Power BI

Published 2018-12-18 by Kevin Feasel

Brad Llewellyn shows how to build a visual based on a Python script using Power BI:

Now that we’ve seen our data, it’s a relatively simple task to convert the R script to a Python script. There are a few major differences. First, Python is a general purpose programming language, whereas R is a statistical programming language. This means that some of the functionality provided in Base R requires additional libraries in Python. Pandas is a good library for data manipulation, but is already included by default in Power BI. Scikit-learn (also known as sklearn) is a good library for build predictive models. Finally, Seaborn and Matplotlib are good libraries for creating data visualizations.

In addition, there are some scenarios where Python is a bit more verbose than R, resulting in additional coding to achieve the same result. For instance, fitting a regression line to our data using the sklearn.linear_model.LinearRegression().fit() function required much more coding than the corresponding lm() function in R. Of course, there are plenty of situations where the opposite is true and R becomes the more verbose language.

Click through for the full example.

Comments closed

The Power BI Visual Vocabulary

Published 2018-12-14 by Kevin Feasel

Jason Thomas has put together a great Power BI report:

Note that there are some R/Python visuals and currently, R/Python visuals are not available on “Publish to Web”. Hence, I have just used a checkbox on the top of the report to show the images wherever R visuals are used (can be identified by the colorful border around the image). However, you can download the source file and then publish it to your tenant, and see the actual R visuals there in a browser by unselecting the checkbox. You can also look at the pbix file and see the source code behind the visuals.

Definitely check this out.  Jason did a great job.

Comments closed

Experimenting With Power BI Data Privacy Settings

Published 2018-12-11 by Kevin Feasel

Chris Webb takes us through some of the intricacies of Power BI data privacy settings and what it means when data sets are Private:

Not only does it only show the Package Search endpoint, there is a warning that says:
“Some data sources may not be listed because of hand-authored queries”
This refers to the output step in the query that calls the Package Show endpoint with the dynamically-generated url.
Closing this dialog and going back to the Query Editor, if you click the Edit Credentials button, you can set credentials for the data source (anonymous access is fine in this case). These credentials can be set at all levels in the path down to https://data.gov.uk/api/3/action/package_search.

Read the whole thing.

Comments closed