Forum Discussion
Many to Many relationship
Hello,
I have two tables Table A and Table B, in table A is the calendar table we have a column its name is year month this is the format (YYYYMM), table B is a cross-sectional table with the same column if a relationship is made In the two tables, a relationship of many to many is made by these fields, which presents a problem when filtering table B by table A
Thanks
If that does not work, create a dummy calculated date field in the fact table. Say, assume the day to be 1. Then you will be able to build the relationship with this date and the calendar and it will work the way you want to.
You can use a formula like this:
TheDate = VAR yearmonth = CONVERT(YearMonthTable[YearMonth], STRING) VAR year = VALUE(LEFT(yearmonth, 4)) VAR month = VALUE(RIGHT(yearmonth, 2)) RETURN DATE(year, month, 1)
3 Replies
Hi,
I understand perfectly what you are saying and I appreciate it, but in table B it is an Employee Matrix for monthly cuts and I only have the cut month in this format YYYYMM which I also have in my calendar table, that's why I do not have a field with unique values with which to make the relationship.
Thanks
If that does not work, create a dummy calculated date field in the fact table. Say, assume the day to be 1. Then you will be able to build the relationship with this date and the calendar and it will work the way you want to.
You can use a formula like this:
TheDate = VAR yearmonth = CONVERT(YearMonthTable[YearMonth], STRING) VAR year = VALUE(LEFT(yearmonth, 4)) VAR month = VALUE(RIGHT(yearmonth, 2)) RETURN DATE(year, month, 1)
