Slice visuals by region and date from two separate tables

[mariahMC]

I have two separate tables: one is case records that has a date column and a region column, the other table is financial data with a date column and a region column. I would like to filter my visuals by both region and month, the issue is that I cannot get all visuals to filter by month, only region. I created a dynamic date table in my model as well. Here is my model currently where Financial Data and Case records have a many to many relationship based on Region column. How do I create the relationship between these tables so that I can filter by month name too?

[Kedar_Pande]

@mariahMC 

Create one-to-many relationships:
Date[Date] → Case Records[Date]
Date[Date] → Financial Data[Date]

Instead of a direct many-to-many relationship on Region:
Create a Region Table with unique values for regions.
Relate Region Table[Region] to Case Records[Region] and Financial Data[Region] using one-to-many relationships.

Add Date[MonthName] to slicers or filters in your visuals. This will ensure both tables respect the selected month.

Set all relationships between the Date Table and your fact tables (Case Records, Financial Data) to single-directional to avoid ambiguity.
Ensure your Region Table relationships are single-directional unless you explicitly need bi-directional filtering.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[mariahMC]

Thank you very much for this advice!

[Bibiano_Geraldo · Accepted solution]

Hi @mariahMC ,

First, remove the many-to-many relationship between the "Financial Data" and "Case Records" tables based on the "Region" column and create a new table that contains a unique list of regions. This table will act as a bridge between your "Financial Data" and "Case Records" tables.

Create a Region Table

RegionTable = DISTINCT(UNION(SELECTCOLUMNS('Financial Data', "Region", 'Financial Data'[Region]), SELECTCOLUMNS('Case Records', "Region", 'Case Records'[Region])))

Create one-to-many relationships from the "RegionTable" to both the "Financial Data" and "Case Records" tables based on the "Region" column.

Ensure you have a date table with a unique list of dates, if not, you can create a new calendar table by this:

DateTable = CALENDAR(MIN('Financial Data'[Date]), MAX('Financial Data'[Date]))

Create one-to-many relationships from the "DateTable" to both the "Financial Data" and "Case Records" tables based on the "Date" column. 

Now, you can use the "DateTable" to filter by month and the "RegionTable" to filter by region in your visuals.

[mariahMC]

Thank you so much this was very helpful and worked! I didn't like the many to many relationship I had before but didn't know how else to connect the two. I am still learning 🙂

[some_bih]

Hi @mariahMC onl link there are examples how to create relationships in Power BI.

Please note, if you are beginner as user, please use only one to many (many to one) relationship otherwise you will issues with your results (hard to understand results of measures), or if you really want / need other types of relationships put additional efforts to eventualy remodel your data model. This is just best practice.

[mariahMC]

Thanks for the advice, I didn't like the many to many relationship I had before but didn't know how else to connect the two. I am still learning 🙂