Category: Power BI

Reza Rad shows how to use a bookmark to set the default state of a Power BI dashboard and return to it with a single button click:

Using bookmarks for clearing all slicers in Power BI is not a a new function, I have been using it for many months, and advising many people to do it that way. However, I still get a lot of questions in my presentations about how to do that. That is why I ended up writing this post. This post shows you a very quick trick of having a button to clear all slicers, and the magic is all happening with bookmarks. Bookmarks store the state of a Power BI page, and can be used in many scenarios, in this post, I only show you the ability to clear all slicers in a page. To learn more about Power BI; read Power BI book from Rookie to Rock Star.

Click through for an example of how to set this up.

Marco Russo explains why you might want to use the COMBINEVALUES operator in DAX:

When you import data in a Tabular model, relationships are optimized when they are based on a single column that does not have a high number of unique values. Columns that do have a high number of unique values are known as high cardinality columns. If two tables require two or more columns to define a relationship, it is possible to create a calculated column that concatenates the values of the columns used for the relationship, on both tables. This way, the relationship is based on one single column on each side. However, the resulting calculated column will have a higher cardinality than the original columns, which is not good for performance.

Most of the time, the presence of relationships based on multiple columns suggests that a better denormalization of the model is required to obtain an optimal star schema. Nevertheless, for smaller tables or when it is not possible to apply complex transformations, concatenating columns is the preferred way to obtain the relationships required. When this happens with data loaded in memory, the only concern is the cardinality of the resulting column. However, this is not the main concern in DirectQuery mode.

A calculated column in DirectQuery translates its expression into a native SQL expression. When this is used as a JOIN condition to express the relationship in a Tabular model, this could produce a non-optimal query plan, which could introduce unnecessary slowness in the query execution. For this reason, Microsoft introduced COMBINEVALUES, which is a function expressly designed to optimize relationships based on multiple columns in DirectQuery mode.

Read on for an example.

Matt Allington shows how to use Power Query to get details on a top-level item (including all child items) which contains a particular child item:

As you can see in the image below, when a slicer is added to the product table and then you select an item in the slicer, the invoice detail table ONLY shows that single selected product.  But the requirement it to see ALL the items on ALL the invoices that contain that selected product.

Read on, as the solution definitely isn’t trivial.

Stacia Varga shows one way of learning about the relationships between categorical variables in Power BI:

A common way to review categorical variable relationships is to create a cross tab, also known as a matrix, to evaluate the counts for each resulting combination.

For example, in my current data set, I can create a matrix to compare the number of players in two teams, say the Knights and the Sharks, by position and by handedness.

In descriptive analytics, I’m not trying to prove anything by looking at these values. I’m just reporting them. (Although I do find it interesting that there is a preponderance of lefties in these two teams.)

In the business world, I might do something similar by placing product categories on rows and customer geography (country or state) on columns.

Stacia also gives her explanation of descriptive analytics, so check that out too.

Meagan Longoria has some thoughts after a proof of concept using Power BI Embedded:

After making changes and testing your report, make sure to clear any slicer values before publishing, if you have row-level security on a field shown in a slicer and you leave values selected. The selected values will be shown to users when they view the report. For example, let’s say you have created a row-level security role that can only see Product A, but you can see everything, and you left Product A and Product B selected and deployed the report. A user who views the report next and is a member of that RLS role will see the two selected values in the slicer, even though they can’t see the data for Product B on the page. This may not be a big deal for an internal report. But now imagine this is for clients. You don’t want clients to see other clients in the list. This behavior is consistent in the Power BI web service and isn’t specific to embedding. It’s just important to remember this.

There are plenty of interesting notes here, so check it out if you’re thinking of a Power BI project.