Press "Enter" to skip to content

Category: Power BI

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

Using Slicers In Power BI To Filter Chart Categories

Published 2018-12-11 by Kevin Feasel

Prathy Kamasani shows how you can use slicers in Power BI to filter out specific categories in a line chart:

The logic is to create a table with the DAX function UNION. Each Table expression in UNION function represents a value of slicer. Apart from that slicer related value, all the rest of the values are blanks.  It is key to have them as blanks than zero’s, we don’t see any data.

In other words, pivoting the table to turn one measure with several different category values into one measure per category.  If you know the number of categories (4 in this case), this solution can work well for you.

Comments closed

Integrating PowerApps With Power BI

Published 2018-12-10 by Kevin Feasel

Wolfgang Strasser continues a series on the PowerPlatform with a post showing how to integrate an existing PowerApp with Power BI:

When creating a new PowerApp using the Power BI integration, you get an additional data source – PowerBIIntegration that serves as the connection to the Power BI report. Whenever a filtering action occurs in the Power BI report, this information is available in this property.
During the PowerApps creation action I selected the action to add a new form which in the next step needs to get a connection to the Article table (which holds the additional article details).

Check out the entire series too.

Comments closed

Connecting Power BI To Dockerized SQL Server

Published 2018-12-06 by Kevin Feasel

Chris Taylor shows us how to build a SQL Server on Linux Docker container and use it to supply data to a Power BI dashboard:

I (and many others) have done a series of docker blog posts over the last couple of years but they’ve all tended to evolve around spinning up a SQL Server 2017+ container for testing or demo purposes. This is only really the start, think of the bigger picture here, once you have your database environment the world is your oyster.

This blog post will show how we can use SQL Server 2019 CTP2.1 running on Linux (Ubuntu) in a docker container as our data source for a Power BI environment in next to no time!

These steps show a very manual process for completing this setup, if it is something you are looking to do frequently then I suggest creating a Dockerfile and/or yml file and use docker-compose. This way you can have all your setup in one file and it will be a single statement to get your SQL Server 2019 environment up and running.

Read on for the demo.

Comments closed

The Power Of Dual Storage Mode For Power BI Aggregations

Published 2018-12-05 by Kevin Feasel

Reza Rad continues a series on Power BI aggregations by explaining how using the Dual storage mode can make queries faster if you use both Import and DirectQuery sources:

This is not what we actually expect to see. The whole purpose of Sales Agg table is to speed up the process from DirectQuery mode, but we are still querying the DimDate from the database. So, what is the solution? Do we change the storage mode of DimDate to Import? If we do that, then what about the connection between DimDate and FactInternetSales? We want that connection to work as DirectQuery of course.

Now that you learned about the challenge, is a good time to talk about the third storage mode; Dual.

Read on for an example-filled tutorial.

Comments closed

Rounding Errors And Data Type Conversions In DAX

Published 2018-12-04 by Kevin Feasel

Marco Russo explains the rules behind data type conversions in DAX:

Any DAX formula involving arithmetical operators ( + – * / ) might produce a result in a different data type. While this is obvious when you have different data types in the arguments, it could be less intuitive when the arguments have the same data type. Indeed, the result might have a different data type. This is important. Indeed, in a complex expression there could be many operators, but every operator defines a single expression that produces a new data type – that is the argument of the next operator. We will start looking at the resulting data type of the standard operators, showing a few examples later of how they could affect the result in a more complex expression.

Marco shows some relatively drastic differences:  hundreds of dollars when dealing with millions (and any company okay with being off by hundreds of dollars when dealing with millions, please mail me a check for hundreds of dollars).

Comments closed