PowerBI changes filtering behaviour with one to many relationships

Data quality assessment is a task that I find I frequently need to do quickly and efficiently as I work with new data. Say I get two tables with ID’s with different attributes. It’s common to check how many of those ID’s are common, and how many of them might be missing in one table or the other. If the relationship was one to many, it used to be possible to create a table with ID’s from both tables, filter on blanks, and quickly see how many were missing. (The solution here is different if it’s many to many).

I’ve noticed that the “Show items with no data” has changed the way filtering works. Suppose I have two tables, fact and dim. Fact contains the values 3,4,5,5 and dim contains 1,2,3. They are related with a one (dim) to many (fact) relationship with a single cross filter direction.

The show items with no data option will show items that are in the dim but not in the fact. Namely; 1,2. That seems reasonable.

What I’ve noticed, is that you can no longer filter factID where it’s blank.  You would expect that to work, and give you dimID of {1,2},  but it does not.  Instead, it will give you everything in the dimension.

If this were a 1-1 relationship, it would work as described above.  You could filter blanks on either side and get the desired effect.  (The image below is how it behaves)

How the two tables appear under a one to one relationship

How the filter works under a one to one relationship

So if you’re looking to understand what is missing in the 1 to many relationship, the only way to do that is to create a measure in the dimension table, counting the number of factID’s.  Only then, can you filter where they are blank, and see what’s missing.

Fact count exists in the dimension table. The row where dimID is blank and FactCount=3 shows the three records that are in the fact table {4,5,5}, but not in the dimension table

Filtering where FactCount is blank gives us the dimID’s that are not in the fact. This is what we could not accomplish in using the two values and relationship in the third image of this post


So there you have it.  When you filter for blanks on the “many” of a one to many, you will be looking at all of the items in the dimension, instead of just the ones that don’t have a factID.   Here’s is the PBIX file if you want to download it and play.

Far better an approximate answer to the right question, which is often vague, than the exact answer to the wrong question, which can always be made precise. -John Tukey
The plural of anecdote is not data. - John Myles White

Recent Posts

RSS PowerBI blog

  • New Power BI Premium summary and workload metrics available in the admin portal April 22, 2019
    We have rolled out a new high-level summary metrics experience in the Power BI Admin Portal . This new experience replaces the four summary tiles you previously saw : CPU, Memory thrashing, Memory usage and DirectQuery with wide range of metrics measuring the summary of usage in the last 7 days to better portray the health […]
  • Live now, full session catalog for Microsoft Business Applications Summit 2019 April 22, 2019
    Time to get excited – the full session catalog for Microsoft Business Applications Summit is here. Explore every breakout session and workshop coming to the conference, taking place in Atlanta, Georgia June 10 – 11, 2019. Get ready to flex your skills – and build new ones – with all things Power BI and beyond. […]
  • E-Mail Subscriptions for Paginated Reports is Now Available April 19, 2019
    E-mail subscriptions is one of the key features used by millions of SQL Server Reporting Services customers today. Our latest release for “New Feature Friday” is the support of Paginated Reports in e-mail subscriptions in Power BI. Now, for the first time, you can schedule an e-mail subscription with a PDF attachment of your full […]