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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors