Press "Enter" to skip to content

Category: Power BI

Downloading Files from Websites with Power BI

Imke Feldmann takes us through an interesting scenario:

When downloading data from the web, it’s often best to grab the data from APIs that are designed for machine-to-machine communication than from the site that’s actually visible on the screen. Not only is the download usually faster, but you also often get more additional parameters that can be very useful. In this article I’m going to show you how to retrieve the relevant URLs for downloading files from webpages (without resorting to external tools like Fiddler) and how to tweak them to your needs.

Read on to see different techniques for finding a URL to give to end users.

Leave a Comment

Getting a Substring with DAX

Reza Rad shows us how to build out a substring using DAX:

Substring is one of the most common functions in many languages, However, there is no function named Substring DAX. There is a very simple way of doing it, which I am going to explain in this post. Substring means getting part of a string, for example from “Reza Rad”, if I want to get the start starting from index 2, for 4 characters, it should return “za R”. Considering that the first character is index 0. Let’s see how this is possible.

The answer’s not as pretty as a SUBSTRING() function would be, but it’s also not too far off.

Leave a Comment

Creating a Time Dimension in Power BI

Reza Rad walks us through creating a time dimension in Power BI:

I have explained about Date Dimension a lot previously and mentioned why that is needed. Date dimension gives you the ability to slice and dice your data by different date attributes, such as year, quarter, month, day, fiscal columns, etc. Time dimension, on the other hand, will give you the ability to slice and dice data in the level of hours, minutes, seconds, and buckets related to that, such as every 30 minutes, or 15 minutes, etc.

Time table SHOULD NOT be combined with Date table, the main reason is the huge size of the combined result. Let’s say your date table which includes one record per day, has 10 years of data in it, which means 3,650 rows. Now if you have a Time table with a row for every second, this ends up with 24*60*60=86,400 rows just for the time table. If you combine date and time table, you will have 3,650*86,400=315,360,000 rows. 315 Million rows in a table are not good for a dimension table. Even if you store one record per minute in your time table, you would still end up with over 5 million rows.

So don’t combine the Date and Time table. These two should be two different tables, and they both can have a relationship to the fact table.

With that in mind, click through to see how to create the table.

Leave a Comment

Auto-Recovery with Power BI

Prathy Kamasani shows us how to recover lost Power BI desktop reports:

A quick post, how many times in Power BI Desktop, have you clicked on “No, remove the files.” and then say OOPS! Well, I did plenty of times to discover this trick.

In short, you can find those removed files under Temp folder like many other windows application files. Usually, the location will be somewhere like this – C:UsersprathyAppDataLocalMicrosoftPowerBI DesktopTempSaves. This location depends upon which version of Power BI Desktop you have. Beware, these files will be removed whenever you clear your Temp Directory.

Auto-save and auto-recovery are marvelous things.

Leave a Comment

Which Groups can Set Permissions in Power BI

Gilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:

As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.

If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.

Thanks for reading!

Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.

Leave a Comment

Slicing Data by a Character in Power BI

Reza Rad comes up with an interesting hack for Power BI:

I have a table for all customers, and I am showing them all in a table visual in Power BI. However, there are many customers in the list, let’s say 18K+. If I want to search for all customers who have “q” in the name, then I need to either scan the table myself, Or use a slicer with a search box, and search for character “q”, and then select all the names with “q” one by one! something like below is tedious!

Click through for more details and the opportunity to download a sample file.

Leave a Comment

Deconstructing Running Totals with M

Cedric Charlier shows how to turn a running total into a periodic series of events with M:

When dealing with time series, you should always check if your time series are event-based or cumulative. For a time series with a step of one day, an event-based time series will contain the count of events for a given day. A cumulative time series will contain the sum of the event of that day and of all the previous days! In this blog post I’ll explain how to transform a cumulative time series into an event-based.

Click through for the code. You can do this in T-SQL as well by subtracting the value from its LAG()-ged value.

Leave a Comment

Finding Below-Average Sales Per Hierarchy Level in Power BI

Soheil Bakhshi has an interesting problem to solve:

Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.

So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:

– Detect which hierarchy level I am in
– Calculate the average of sales for that particular hierarchy level
– Change the colour of the columns that are below the average amount

Let’s get it done!

Read on to see how you can do exactly this.

Leave a Comment

Using DAX’s GroupBy Function

Reza Rad takes us through the GroupBy function in DAX:

There are many different ways you can create aggregations in Power BI, You can do it in the source (using the database t-SQL language), or using Group By operation in Power Query. You can also do it in DAX using some functions. One of the functions that can be used for grouping and aggregation is Group By. This article is about how to use Group By in DAX. Creating aggregation using DAX is a very useful skill because you can use it to create virtual tables in your measures and have better dynamic calculations in Power BI.

Click through for the explanation and an example.

Leave a Comment