March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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.
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
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
132 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
203 | |
141 | |
107 | |
73 | |
70 |