Forum Discussion
Filter two visuals from two tables with one date filter
Goal: Create a page that displays data from two tables, but filters by one common date.
Here are the two main tables. Each visual is connected to a separate table, Reviews and Misc. The main active relationship is between the Coaches so we can see each coach individually. This is working as you can see both tables are filtered to Angelo.
Both tables have a similar date, Invoice date. I found documentation on creating a custom measure using a USERELATIONSHIP function, but that didn't seem to work since I want to show more data than just the Pay Amount.
I found a potential solution using a Calendar table, which I created. It seemed like as long as the Cross Filter Direction is set to single, this would work. However, when managing the relationships I still get an error saying I need to make all relationships Single, but they are.
All relationships are set to single, but I still can't make the relationship from the Date table to the Misc table active. What am I missing?
Thanks in advance.
Hello bralenj ,
then I would suggest to work your model as a star schema.
check the concept of star schema data modeling
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
5 Replies
- AmiraBedh
Super User
Can you please share your pbix file ?
- Idrissshatila
Hello bralenj ,
the thing with the relation between calendar table and the misc table isn't active is that the combined view is filtering the combined misc and the combined view is being filtered by calendar table.
if you don't need the relation between the combined view and rhe combined misc table you could remove it and then you could make the relationship between the calendar table and the combined misc table active and it will work.
My challenge with this is I also want the relationship between the coaches. If I remove that relationship in favor of the date one, then I would need two filters for coach, one from each table. So it would create the same issue, just for a different field.
- Idrissshatila
Hello bralenj ,
then I would suggest to work your model as a star schema.
check the concept of star schema data modeling
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
