Forum Discussion

jgil's avatar
jgil
Frequent Visitor
6 years ago
Solved

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 sam...
Need Help
Show and Tell
  • sanimesa's avatar
    sanimesa
    6 years ago

    jgil

    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)

     

sanimesa's avatar
sanimesa
Post Prodigy
6 years ago

jgil  You should create a one to many relationship between the date column of your calendar table and the date column of your fact table. Then you can use the Year month field to filter.

  • jgil's avatar
    jgil
    Frequent Visitor
    6 years ago

    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

    • sanimesa's avatar
      sanimesa
      Post Prodigy
      6 years ago

      jgil

      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)