Establishing multiple table relationships

[Anonymous]

Hi,

I wanted to know how to build multiple relationships between tables. I have the following:

- Date table
- Population table (which includes a unique ID and date field, plus other columns)
- Media table (which includes a unique ID and date field, plus other columns)

I'd like to be able to use report level filters with relative date filtering in order to switch easily between a daily or monthly view, so I need to be able to link my Population and Media tables to the Date table in order to do this. However, I also need to be able to cross-reference data between the Population table and Media Table (and eventually other tables as they are added) so that I can cross-reference and combine data in visuals - this would be done using the Unique ID.

Is there a way of doing this? I realise I can add visual level filters to control date, but I want to be able to easily switch at the report level. Any help or advice would be great!

Many thanks

Chris

[v-juanli-msft · Accepted solution]

Hi @Anonymous

If you split tables before import data into Power BI, please ingore the words below.

How do you split one table to many tables, do you use Edit Queries or create a New table(Home ->New Table) with DAX?

If you use create a New table with DAX, please note the circle dependence problem.

please read this article to learn how to create relationship among tables

1. Date table

2.Population table

linked column: [date]

cardinality: one to many 

cross filter direction: both

1.Date table

2.Media table

linked column: [date]

cardinality: one to many

cross filter direction: both

1.Population table

2.Media table

linked column: [unique id]

cardinality: one to one

cross filter direction: both

Best Regards

Maggie

[Anonymous]

Thanks Maggie, that worked!

[richbenmintz]

Hi @Anonymous,

The first part of your ask seems pretty straight forward connect the Date Table to the Population and Media Tables through a 1->* relationships, this will allow you to filter both tables with a single date. The second part is more ambigious, how are the uniqueids related, is media child of population or vice versa, are the uniqueIds related to another seperate dimension? 

Are you able to provide a sample file or some sample data?

Thanks,

[Anonymous]

Hi @richbenmintz,

Thanks for replying so quickly. I'm afraid I can't share any files, but I can hopefully add something further. The data is coming from a survey; the unqiue ID gives a reference for each respondent / row of data, as we wanted to break the survey up into a series of smaller tables to make navigation and use of the dashboards easier. We've therefore got the unique ID as a common link between all of the tables. So, population is one 'cut' of data from the survey; media is another cut, and so on.

Hope this helps.

Best

Chris