topic Re: One Filter for Shared Column and Values Across Multiple Tables in DAX Commands and Tips

One Filter for Shared Column and Values Across Multiple Tables

MattSwan — Thu, 18 Nov 2021 10:26:26 GMT

Hi there,

I have multiple tables all linked via an ID column.

The data is all pupil information. There is a single pupil table with no Academic Year table, linked to tables such as attendance, behaviour and assessment which all contain a column for Academic Year. This creates one (pupil) to many (attendance, behaviour and assessment) relationships.

In some case (not all), I would like to be able to filter multiple visuals on a page using data from different tables using a single Academic Year slicer/filter.

I thought I'd be able to create an Academic Years table and create relationships, but since the tables are already linked using the pupil ID it won't activate them as it says it will introduce ambiguity.

Is there a better way of achieving what I'm trying to do?

Thanks!

Re: One Filter for Shared Column and Values Across Multiple Tables

Gabriel_Walkman — Fri, 19 Nov 2021 07:43:16 GMT

It shouldn't introduce ambiguity, from what I gather.

Show us the model. I hope you are using single cross filter direction, arrows going from pupil table to the others?

Re: One Filter for Shared Column and Values Across Multiple Tables

Anonymous — Wed, 24 Nov 2021 07:45:44 GMT

Hi MattSwan ,

Do you consider designing its relational model as a star architecture

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.