Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

1:M Relationship and Missing Matching Record on the 1 Side - Filter/Slicer Behavior

When modeling within Power BI, more often than not the user will create 1:M (one-to-many) relationships from the Dimension table(s) to the Fact table(s) when following the dimensional modeling principles. However, there might be instances where not all records in the Fact table(many side) will have a matching record in the Dimension. When this scenario happens, can a slicer be setup in order for a report user to select (blank) in the slicer which then filters down the records to only those that don't have a matching record in the Dimension that the slicer is based on ?

 

For instance, based on the below example, if I create a 1:M relationship from the Dim(table 2) to my Fact(table 1), can I create a slicer based on the "Product Size" where the user can then select (blank) from the slicer and then only see the applicable records? In this case the user would only see the second record from the Fact(table 1) as it doesn't have a matching record in the Dimension(table 2) table for Product Key 3. Is this the sort of behavior I can expect with slicers when a matching Dimension record doesn't exist ?

 

 

Table 1 Fact

Plant    Quantity     Product Key

111       50.00           2

222       35.00           3

444       60.00           2

 

Table 2 Dim

Product Key     Product Name    Product Size

2                       Plastic case        10 inches

4                       Metal Case        12 inches       

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for your reply. I understand the referential integrity issue but that's not what I asked about. I'm interested in the behavior of the slicer based on the scenario I outlined in my post. 

 

I ended up creating the two tables I outlined, created the relationship and then created a slicer based on the "Product Size". When I selected (blank) in the slicer, the records filtered down to only the records that didn't have a Product Size value. I purposely created the Fact table records where at least one record didn't have a matching Product ID value in the Dimension table. This exercise answers my question.

 

 

View solution in original post

3 REPLIES 3
Veera12
Frequent Visitor

Hi everyone 

I'm facing one issue 
I have  3 tables table1 (file column(data like policy+filename & filename)) and table2 filecolumn(imported table where file =policy+filename  )and table3 col1(imported table where file !=policy+filename (added new column col 1append policy+filename) )
connected table3 and table1 with many to one relation(by using file & col1 columns)

connected table2 and table1 with many to one relation(by using file columns)
in my report view  i have 3 tables 1 bar chart and filter search
if i filter the some data I'm able to see that data in two tables and bar chat but I'm not able to see in one table why? and how to get my data
I have seen data types same, there is no spaces connection also active  

3

HotChilli
Super User
Super User

I suppose the answer is that you could write some functionality to do this BUT in my opinion it's solving the wrong problem because the situation shown has a referential integrity problem.

A dimensional record not linked to any fact record? No problem

A fact record with a value in a dimension field that is not in the dimension? That's a problem.

e.g. Pull the Product Name into a table visual with Quantity from the Fact table. It's going to show 35 units of nothing.  And if there are other Fact records that don't have a Dimension record, I'm pretty sure it's going to add them in to the 35 units of nothing.

Anonymous
Not applicable

Thanks for your reply. I understand the referential integrity issue but that's not what I asked about. I'm interested in the behavior of the slicer based on the scenario I outlined in my post. 

 

I ended up creating the two tables I outlined, created the relationship and then created a slicer based on the "Product Size". When I selected (blank) in the slicer, the records filtered down to only the records that didn't have a Product Size value. I purposely created the Fact table records where at least one record didn't have a matching Product ID value in the Dimension table. This exercise answers my question.

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.