Forum Discussion

Victormar's avatar
Victormar
Icon for Helper V rankHelper V
4 years ago
Solved

Defining date relationships without having a date field

Hi all,

 

I am working in a project where I pull data from many different excel sheets, the kicker is that non of this excels has a date field, they have many months and years, and I would to create a relationship among the tables, as usual, using the date field. I have created a calendar table, but of course when trying to create a relationship with the other tables it says that the relationship is going to be many to many and it's not recommended. There is no other connection between the tables, no index or similar that I could use, every table is different, they only share month and year. 

 

What would be the most efficient was, if any, of creating this date relationship in order to create hierarchies and connect the different tables?

 

Many thanks in advance

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Victormar .

    1)If you have month and year in your table, then you can create a Date Column using DAX like ;
    Date=DATE(Table[Year],Table[Month],1).
    2)Create a Date table and then try to create a relationship between the tables.

    3)If your data hierarchy is like one field in one table refers to many fields in another table, then you will have to create a 'Many to Many' relationship as other relationships would throw an error.
    4)Although it is not advisable to create a 'Many to Many' relationship, if your data is such then you can create a relationship and have the cross filter direction as dimension table filters fact table.
    Don't have a cross filter direction as 'both'.
    5)If you still don't want to go for 'Many to Many', then you can refer the below links;
    https://community.powerbi.com/t5/Desktop/Avoiding-Many-To-many-relationships/m-p/675653
    https://www.burningsuit.co.uk/dealing-with-many-to-many-relationships/




    Thanks,
    Sanket

    If this post helps, then mark it as 'Accept as Answer' and give it a thumbs up. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Victormar .

    1)If you have month and year in your table, then you can create a Date Column using DAX like ;
    Date=DATE(Table[Year],Table[Month],1).
    2)Create a Date table and then try to create a relationship between the tables.

    3)If your data hierarchy is like one field in one table refers to many fields in another table, then you will have to create a 'Many to Many' relationship as other relationships would throw an error.
    4)Although it is not advisable to create a 'Many to Many' relationship, if your data is such then you can create a relationship and have the cross filter direction as dimension table filters fact table.
    Don't have a cross filter direction as 'both'.
    5)If you still don't want to go for 'Many to Many', then you can refer the below links;
    https://community.powerbi.com/t5/Desktop/Avoiding-Many-To-many-relationships/m-p/675653
    https://www.burningsuit.co.uk/dealing-with-many-to-many-relationships/




    Thanks,
    Sanket

    If this post helps, then mark it as 'Accept as Answer' and give it a thumbs up. 

    • Victormar's avatar
      Victormar
      Icon for Helper V rankHelper V
      4 years ago

      Thanks! That worked perfectly well, thanks for the links as well, now I understand it much better.

      Cheers 🙂

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    you can calculate column in your table by adding first day of the Year_Month and then create your relationship either in power query or dax
    In power query you can do something like this

    In dax something like this

    Date = date('Table'[Year],'Table'[Month],"01")
    and again create the relationship

     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !