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

[Anonymous]

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       

[Veera12]

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]

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 · Accepted solution]

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.