Search code examples
powerbidaxpowerbi-desktoppowerbi-datasource

How to make a 1:1 relation between dates

I have a table about occupational accidents in a Company. This table have a date column about when this accidents happened. What I want to do is make another table about occupational accidents dates with day, month and year columns which is joined with the another table by the date I extracted from the Occupational accidents table (In my database known as OPS table.

I thought it would be easy but when i tried to do the relationship beetween OPS[Fecha](This is the field date from OPS table) and Date[ID](Date is how i called my new table and ID is the field joined with the another table), the relationship returns me as many to many what makes no sense because i think it should be 1 to 1.

This is what i did and my result:

1.This is the table I'm talking about.

OPS Table

  1. I added as a new query:

Fecha

  1. Convert to table.

Table Fecha

  1. I changed the column name to ID

column name

5.Then I try to make the relationship between these 2 tables waiting to get a 1 to 1 relation and I get this:

Problem

What I want to get is something like this relation:

solution

Solution

  • When you add a new query and after converting it to a table, add a new step to remove duplicates(screenshot below). Once this is done you will be able to create a One-to-Many relationship between these 2 queries. But you CAN'T be able to create One-to-One.

    remove duplicates

    Also, please search online on how relationships work in Power BI. I recommend this video: https://www.youtube.com/watch?v=-4ybWQSRcOY