Press "Enter" to skip to content

Category: Power BI

Tips for Power BI Modeling with ADX

Dany Hoter shares some tips on creating star schema models with Azure Data Explorer:

Relationships between DQ tables are created as M:M by default. This is not a problem and even recommended with single direction.

Read on for several tips. What’s interesting as I read this is just how radically different the advice is for ADX utilization versus Power BI utilization, such as using strings to join dimensions to facts. That would be heresy in a Kimball-style model and is a common cause for slow-down in Power BI. Yet that’s the recommendation here for working with ADX, unless I’m misunderstanding Dany’s post.

Comments closed

Power BI Group By Columns

Marco Russo and Alberto Ferrari bundle things together:

In Power BI you can specify the unique identifier of a column value by using another column or another set of columns. This feature is currently used by the Fields Parameter feature in Power BI, but it may also be used for other purposes in a model. However, there are several limitations – such as the incompatibility with MDX queries – that reduce one’s ability to use Group By Columns property in many scenarios, so it cannot be used with Excel as a client.

Read on to learn more about how grouping works in Power BI and some of the limitations.

Comments closed

Building a Dimension and Measure Matrix for Power BI

Olivier Van Steenlandt does some documentation:

In this blog post, I will guide you through all the required steps to get a Data Model Relationship Matrix in Power BI.

If you don’t know what I mean, I would like to have a straightforward overview where I can see which attribute groups and measure groups I can combine from my Tabular Model in (SQL Server) Analysis Server.

The first thing I thought of was “this is very much like a bus matrix in the Kimball model.” It’s a little different, though, as the rows in the axis pertain to measure groups rather than business units.

Comments closed

Saving Incremental PBIX File Backups

Matt Allington saves PBIX Final Report V2 No Wait V3 Draft Demo 2 Copy Copy Copy 3.pbix:

I do a lot of Power BI model and report development; maybe you do too. There’s nothing worse than spending an hour or so developing your model only to have something go wrong and you lose your work. Things that can go wrong include:

  • Your PC/App crashes. Power BI does have auto save, but I prefer that to be the last thing I rely on to save the day rather than the only thing.
  • You make a significant mistake in the approach and need to undo your work (autosave won’t save you with this problem).
  • You make a big mistake in Power Query. There is no undo in Power Query, so if you spend an hour inside Power Query, don’t save, and then make a mistake, there is no way to recover your work.

At the time of writing, there are no version control tools built into Power BI, so as a result it is up to you to manage backups yourself.

Read on for a few tips around backups and file management.

Comments closed

DirectQuery Support for ApproximateDistinctCount DAX Function

Chris Webb has an update for us:

Some good news for those of you using DirectQuery mode in Power BI: the ApproximateDistinctCount DAX function, which returns an estimate of the number of the distinct values in a column and which can be a lot faster than a true distinct count as returned by the DistinctCount function, is now available to use with BigQuery, Databricks and Snowflake sources. It only worked with Azure SQL DB and Synapse before; RedShift is coming soon. You can use it in exactly the same way that you would with the DistinctCount function except that it only works in DirectQuery mode.

As always, there’s an example. I do wonder if the DAX function uses the same HyperLogLog algorithm that SQL Server uses for its approximate count distinct.

Comments closed

Power BI Scanner API Updates

Matthew Roche has an update for us:

Power BI includes capabilities to enable users to understand the content they own, and how different items relate to each other. Sometimes you may need a custom “big picture” view that built-in features don’t deliver, and this is where the Scanner API comes in.

Read on to learn what the Power BI Scanner API is and some of the most interesting updates. Matthew also has a link to the announcement with a full set of updates.

Comments closed

A Primer on Power BI Apps

Melissa Coates offers up an explanation:

If you hear the question: “What’s a Power BI app?” you might think there’s a straightforward answer. However, the term ‘app’ is pretty overloaded term in the world of Power BI.

One time I was presenting a session and talking about Power BI apps vs. workspaces – right in the middle, someone in the audience starts asking me a Power Apps question because they thought I was talking about that (which is a VERY different thing). That was a big reminder to not to assume that we all have the same understanding of terms.

Read on to learn what a Power BI app is, as well as why it’s a rather useful concept.

Comments closed

Text Search Performance Optimization in Power BI

Chris Webb provides advice about a relatively new feature:

In the blog post for the October 2022 release of Power BI Desktop there’s a brief description of a new optimisation in Power BI for filters that perform searches in text columns. In this blog post I’m going to share more details about this optimisation and how you can make sure your reports can benefit from it.

Read on to learn a bit more about how it works, as well as a few tips on ensuring that you’re able to take full advantage of this optimization.

Comments closed

Options to Export Power BI to Tables

Gilbert Quevauvilliers counts the ways:

I was recently helping out a customer and they contacted me asking why was the export option not in the format that they expected.

I had a look and now because there are so many options to export data, each one exports the data differently.

My goal for this blog post is to show you what each export type looks like, so when a user is exporting data, they can export in the format they expect.

It turns out that there are several such ways and Gilbert describes each.

Comments closed

Removing Diacritics with Power Query

Chris Webb gets rid of them scribbles what you sometimes find on perfectly good letters:

…then the output is “un garon trs g Nol”. As you can see, removing all the characters leads to unreadable text. Instead, what you have to do is find all the letters with diacritics (accents and other glyphs that can be added to characters) and remove the diacritics. Doing this may be ungrammatical and make it harder to understand the meaning of the text but in most cases the text will still be readable.

The bad news is that there is no straightforward way to do this in Power Query, and indeed there is no straightforward way to do this at all because there are no hard-and-fast rules about what to replace a letter with a diacritic with: should “ö” become “o” or “oe” for example? My first thought was to create a big lookup table with all the rules of what to replace each character with in, similar to the approach taken here for solving this problem in Google Sheets. Building a comprehensive lookup table would be gigantic task though.

Chris does take a different approach, though do read the comments because there are scenarios in which a simple removal of the diacritic can lead to a not-so-subtle alteration of the phrase.

Comments closed