Forum Discussion

giroldoale's avatar
giroldoale
Icon for Helper I rankHelper I
5 years ago
Solved

Connecting two tables with dates

Hi,

I have some doubts with relationship through dates.

My aim is to have a single table called "Calendar" where I connect all the DataTable with dates, so use only the date into "Calendar" to works with time across the report.

 

I have 2 cases in the screenshot, both the table "Calendar" and "Calendar 2" have the same data set. 

"Calendar", and so "Calendar 2", is a LUT table having only the function to generate a calendar from 2018 to date. In this sense in these two table there are no repeting dates.

 

In "Sales" there are dates from Jan/21 to date and for every day the corresponfding value of Qty and Value sold. Every dates is unique except to 08/01/2021 (DD/MM/YYY) that is repeated 2 times.

In "Sales 2" there are dates from Jan/21 to date and for every day the corresponfding value of Qty and Value sold. Every dates is unique.

Recaping the only difference between the two cases in term of data is a single row into Sales that is repeted 1 time.

 

Now the questions:

  1. why in the report once I connect the filed "Data" (Date in Italian) the Hierarchy in the case 1 fo "Sales" is cancelled?
  2. why in the case 2 I cannot chooose the direction of relationship?
  3. why in case two if I drag Data from "Sale Unic" to "Calendar 2" the Hierarchy in Data of "Sales Unic" is cancelled? And if I do the opposite, so drag Data from  "Calendar 2" to "Sale Unic" the Hierarchy in Data of "Calendar 2" is cancelled? Even if the arrow of relationshiop is the same?

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi giroldoale ,

     

    For the second question, to my knowledge, the relationship is always from 1 to many.  In the case of 1: 1, it could not identify which is "many".Therefore, 1:1 can only be a one-way direction.

    For the third question,it is intended behaviour, in this case you will use date field from date table to work in your visuals.Refer to :Solved: Re: Date hierarchy missing - Microsoft Power BI Community

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

43 Replies