Press "Enter" to skip to content

Category: Power BI

Case Sensitivity in Power BI

Published 2023-10-19 by Kevin Feasel

Kurt Buhler is going to raise my blood pressure this morning:

Most Power BI models are case-insensitive, meaning that “Bonk” is the same as “BONK”. However, Power BI data models can also be created as case-sensitive if you create a Direct Lake model in Fabric, or create a new model with external tools and enter a case-sensitive collation property. Two otherwise identical models which differ only in this case-sensitivity may produce different results, even though they’re using the same data, DAX, relationships, and tables.

It’s useful to know how case-sensitivity affects your model and its query results. You should also be able to identify and validate whether your model is case-sensitive. This is particularly important in the following scenarios:

Read on for those scenarios and how you can fix the problem of case sensitivity. My official stance on case sensitivity, by the way, is that applications should be case-insensitive on input but retain casing on output, so “dog” = “Dog” = “DOG” for sorting and querying, but if I saved “Dog” then that’s what should display.

Comments closed

Monitoring Power BI Gateways with Microsoft Fabric

Published 2023-10-18 by Kevin Feasel

Tom Martens builds a solution:

No matter what, when the on-premises gateways are not working as expected, data will not refresh, and direct query queries will not succeed. For this reason, I consider it a good idea to track the well-being of these valuable resources. This article describes a solution built with Microsoft Fabric. It’s not necessary to use Fabric, and it’s also not necessary to build a solution on your own. If you want to track the well-being of your on-premises data gateways but do not want to build something, I recommend using the solution by Rui Romano you can find here: https://github.com/RuiRomano/pbigtwmonitor

I built this monitoring solution focusing on the well-being of the on-premises data gateway. I might extend this solution in the future, but for now, it’s about the availability of the on-premises data gateway and the data gateway connections. Availability and analysis will follow during the next weeks.

Click through for Tom’s solution.

Comments closed

Using Tableau with Power BI and Fabric

Published 2023-10-17 by Kevin Feasel

Kurt Buhler crosses the streams:

If you use Power BI, Fabric, or Excel, connecting to Power BI datasets is straightforward. However if you use other BI tools like Tableau, it’s not obvious how you can leverage a Power BI semantic model in your workflow. In this article, I’ll explain how to connect to and use a Power BI dataset from Tableau Desktop.

Read on to see how. Also check out the notes in drill-down sections, as there’s a lot of content in there.

Comments closed

Excel Data Analysis with Python

Published 2023-10-16 by Kevin Feasel

Chris Webb takes us through a new add-in for Excel:

In the Power BI/Fabric community everyone is excited about the recent release of Semantic Link: the ability to analyse Power BI data easily using Python in Fabric notebooks. Sandeep Pawar has an excellent blog post here explaining what this is and why it’s so cool. Meanwhile in the Excel community, everyone is excited about the new integration of Python into Excel. But can you analyse Power BI data in Excel using Python? Yes you can – so as my teenage daughter would say, it’s time for a crossover episode.

Click through for an example of it in action.

Comments closed

Updates to Power BI Field Finder

Published 2023-10-13 by Kevin Feasel

Stephanie Bruno has an update for us:

The Power BI Field Finder is a standalone .pbix file you can download and hook up to your reports and data model to. The Field Finder helps you visually analyze where fields are used in reports.

I’ve used this to great effect on a prior project where I had to figure out what was going on in a report with about 20-25 pages that other people had put together.

Comments closed

Refreshing a Direct Lake Power BI Dataset in Microsoft Fabric

Published 2023-10-12 by Kevin Feasel

Chris Webb refreshes our memories:

If you’ve heard about the new Direct Lake mode for Power BI datasets in Fabric you’ll know that it gives you the query performance of Import mode (well, almost) without the need to actually import any data. Direct Lake datasets can be refreshed though – in fact, they refresh automatically by default – and if you look at the dataset’s Refresh History you’ll see there’s a Direct Lake section which sometimes shows errors:

Chris goes on to ask and answer the question, what does it mean to refresh a Direct Lake dataset if you’re not actually importing the data into Power BI?

Comments closed

Conditional Formatting in Power BI with Field Parameters and Calculation Groups

Published 2023-10-10 by Kevin Feasel

Marco Russo and Alberto Ferrari perform some formatting:

If you want to build a report where the user can choose what measure to show, you have two features available in Power BI: field parameters and calculation groups. There are pros and cons to either technique – however, we are not about to talk about those. We narrow our scenario down to a specific requirement: we want to change the color of the value depending on the measure selected.

For example, suppose we let users choose between Sales Amount, Margin, or Total Cost. In that case, we might provide visual feedback about the measure selected through different colors: black for Sales Amount, green for Margin, and red for Total Cost.

Click through for that example, though I will say that the color choices are hard to differentiate if you have protanopia and even more difficult if you have deuteranopia, so about 2% of the male population would struggle with interpreting this measure. People with protanomaly and deuteranomaly (about 6% of men) wouldn’t have too much difficulty with this particular color pairing.

Comments closed

Power BI Themes and Gallery

Published 2023-10-09 by Kevin Feasel

Seth Bauer has an announcement:

The Power BI Tips Theme Generator tool already allows you to easily interact with, and adjust, all the visual properties, wireframes, etc… How could we possibly make Power BI Theme building an effortless experience? We start with building it all for you, then letting you adjust it!
The all new Gallery feature represents a significant leap forward in simplifying the theming process for all. This feature is especially for the business users! But, it also opens up exciting opportunities for the Power BI community to contribute in the future as well.

Read on to see how this works.

Comments closed

Always Use Explicit Measures in Power BI

Published 2023-10-05 by Kevin Feasel

Ed Hansberry has some advice for us:

All of your analytics in Power BI are done with measures, and there are two ways to create measures. It is important to understand the difference between the two methods. If you drag a field from the data pane directly into a visual, that is an implicit measure. The model usually wraps it with SUM(), but other aggregations are possible, like COUNT() or AVERAGE().

Read on to see what’s wrong with using implicit measures, as well as how to make these explicit.

Comments closed

Distinct Counts in Power BI and KQL

Published 2023-10-04 by Kevin Feasel

Dany Hoter needs a distinct count:

Calculating distinct counts on massive distributed datasets is not trivial.

Kusto (aka Azure Data Explorer/KQL database in Fabric) dcount and dcountif functions use a special algorithm to return an estimate of distinct counts

The new functions count_distinct and count_distinctif were recently added to calculate exact distinct counts. These two functions are much more expensive than the original ones.

Read on for more details on how this all works.

Comments closed