Data model in Power BI

[Anonymous]

Hi all,

I have two tables.

Table 1 : Awards (Fiscal year_Award Date, Award date , Sponsor etc.)

Table 2 : Submissions (Fiscal Year_Submission Date, Submission Date, Sponsor etc.)

I have two tabs in the report.

Tab 1 for submissions which has Fiscal year_Award Date and Sponsor as a slicer

Tab 2 for Awards  which has Fiscal year_Submission Date and Sponsor as a slicer

Now, I want to have all the slicers in sync in both the tabs.

As the fields are different and  are coming from different tables, I am unable to achieve this.

Can some one please let me know the best approach for this.

Regards

Poojitha

[ibarrau]

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,

[Anonymous]

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.

[ibarrau]

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