Filter by month number for all tables

[takeiteasy]

Hello to power bi community. I am newbie and I want your advice about something.

I have tables that works only with number of month. I want to create a month table so to be able to filter by month.

Let me give you an example:

I have 3 tables: CustomerTransactions, SupplierTransaction, PersonalTransactions

They look like :

Month	CustomerID	ItemID	SalesManID	Value
1	120	1	1	120
2	120	1	2	100
1	121	2	1	130

SupplierTransactions:

Month	SupplierID	ItemID	SalesManID	Value
1	10	1	1	120
1	11	1	2	100
2	11	2	1	130

PersonalTransactions:

Month	EmployeeID	ItemID	Value
1	10	1	120
1	11	1	100
2	11	2	130

What I want is to create a month table that 1: they will give me back the name of the month 2: They will filter other tables based on selected month on the slicer.

I have create a table with the months numbers and name but when I am trying to connect two tables with the month table, it is says that this relationship will be inactive since it will create a conflict between an another table and Month.

Month table looks like:

MonthID	MonthName
1	January
2	February

How can I overcome this?  

[SolomonovAnton · Accepted solution]

hello

create a relationship like in the picture below

[v-yanjiang-msft]

Hi @takeiteasy ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @takeiteasy ,

According to the error message, I think it's caused by ambiguity relationship, it means for example SupplierTransactions have two path to reach PersonalTransactions, you have to choose only. Additonally, you can't create closed loop relationship, it means for three tables, you can't make three active relationships between each other like below.

Check whether the relationship of the three tables conforms to the situation I described above, then if you can't solve it by modify the relationship, please indicate their relationship here.

Best Regards,
Community Support Team _ kalyj

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

[SolomonovAnton · Accepted solution]

hello

create a relationship like in the picture below