Forum Discussion
Slicer not functioning for directly related fact table
Hi All
Thank you for your attention to this question, I appreciate your time. I am new to PowerBI.
Overall Context: I have a data table which holds the IDs of various 'WBS' codes, each of which basically refers to a funded project.I have multiple tables which refer to the WBS-Table in a Star Schema (I think!).
The issue: I am seeing strange behaviour for my Budgets table.
Specific Context: Budgets also has the WBS field and is linked to the WBS Table (1:Many | WBS-Table:Budgets)
The behaviour: When I have a slicer on a field in the WBS-Table such as 'Area', it is not slicing the Budgets data, though it will slice other data sets fine (which have the same 1:Many relationship through the WBS field.
I have de-identified my data and removed some other helper tables.
https://drive.google.com/drive/folders/1BxWCSlM2LHsjkbmnf3WIIl-w8E4C52Ve?usp=sharing
Thank you again
HerdedByDorpers
The WBS table cannot filter the Budgets table because the relationship is inactive (that's hard to see in the model diagram due to the scale but the line is dotted).
Powerbi won't let the relationship be active because there would then be 2 paths between WBS and Actual By Month tables.
There are ways around this but it's really telling you that the model doesn't work.
---
I'm not sure what visuals and measures you want to explore in your data so I can't say "Here's the model you need" but there does seem to be a duplication of fields in different tables and I don't like the many to many relationship that's there.
--
Have a think about what you want in your visuals, simplify the model and go again is my advice.
2 Replies
Thank you HotChilli
I had to make a few changes to address the issues and make it fully operational.- I removed the problematic many-many relationship to the date table.
- I removed other conflicting relationships and made sure that they were cascading 1:Many relationships.
- I reestablsihed the original 1:Many relationships and made them active.
- I set up a proper date table
- To link date tables to my monthly data I needed an extra field which had the first date of each month so that the link would be 1:Many.
- HotChilli
Community Champion
The WBS table cannot filter the Budgets table because the relationship is inactive (that's hard to see in the model diagram due to the scale but the line is dotted).
Powerbi won't let the relationship be active because there would then be 2 paths between WBS and Actual By Month tables.
There are ways around this but it's really telling you that the model doesn't work.
---
I'm not sure what visuals and measures you want to explore in your data so I can't say "Here's the model you need" but there does seem to be a duplication of fields in different tables and I don't like the many to many relationship that's there.
--
Have a think about what you want in your visuals, simplify the model and go again is my advice.
