Forum Discussion
Data model in Power BI
Hi. I don't know how your model is built in order to avoid breaking some relationship. Anyway, you can add a Calendar Table and relate it with both your fiscal award dates and fiscal submision date. That way you will only use dates from the Calendar Table that would be sync by you in the two tabs. Because they are related, it will filter the data by the correpsonding date.
You can use the same example for Sponsor. Building a table with unique values of sponsor as a Dimension to sync filters.
Hope that helps,
Hi ibarrau
I tried this approach as shown below:
I used Fiscal year slicer from FY and Sponsor slicer from FY(2), but with this model, the slicers in the same page are not getting filtered based on the selections as the fields are coming from different tables.
Eg: I selected FY as 2021, the FY gets synced now in both the tabs but the Sponsor slicer is not getting filtered out for 2021 but it shows all the values.
Is there a way to filter out the sponsor values as well based on the selections on Fiscal year in the same tab,
i.e, on a selection of a value in one slicer, the other slicer should be filtered out.
That's a confusing idea for users. Synced slicers that will filter each other... think that in order to filter a sponsor by FY you have to know the submission and awards first. You might be able to build two approaches but both confusing:
- Measure for filter: adding measures as visual level filter to pair the dimension. The problem here is that if you filter a FY like 2020 you will see only sponsors that are in both submission and awards at 2020. https://blog.ladataweb.com.ar/post/185907335292/datamodeling-sincronizar-filtros-entre
- Changing everything to bidirectional (relationships): this is a bad idea on a performance standpoint. I think this options will let you filter a FY like 2020 and you will see sponsors that match at least one of the tables. If you are in tab 1 awards you will see sponsors from submision that will result in blank data if users filter. You can also build this approach adding a || OR statement on the previous measure.
That's why it's better to let the user understand that you have the whole list of sponsors there, becuse filtering it might get worst considering the syncronization.
Hope that make sense