Forum Discussion

yanivshe's avatar
yanivshe
Helper I
7 years ago
Solved

Mark as date table - How to

Hi,

 

I'm having trouble modeling my tables.

I have 2 tables of orders:

- Open_Orders_Table: [order_id, open_date]

- Close_Orders_Table: [order_id, close_date]

 

I want to show the ammount of open orders vs close orders per date.

So what I did was to create a third table, a Date_Table, and mark it as date table.

The Date_Table is quite simple and contains only dates columns.

 

I setup the relations between the Date_Table and Open_Date on the date column ((1:1 relationship)

And formatted the date to be only date value (no timestamp)

 

When I put the 2 fields in a table visual , it is empty :(

 

I tried to remove the "mark as date table" but it didnt work as well...

I'm guessing that the solution for the Open_Orders_Table will be the solution for the Close_Orders_Table

 

Any one has an idea what I need to do?

 

 

 

 

Need Help
  • yanivshe's avatar
    yanivshe
    6 years ago

    I did some more investigation on this:

    OPEN_ORDERS table starts from: 2006-04-26 to: 2019-09-04

    DATES table starts from: 2006-04-26 to: 2019-09-12

     

    Filtering DATES[date] to start from 2006-04-27 -> OPEN_ORDERS[date] starts is from: 2006-04-27 to: 2017-03-03

    Filtering DATES[date] start from 2006-04-26 -> OPEN_ORDERS[date] is from 2006-04-26 to: 2019-09-04

    Looks like the first DATES[date] is curropted beacuse when I filter on it, all OPEN_ORDERS[date] after 2017-03-03 appear

     

    Does anyone encountered a DATE table with Date field which the first date value curropted his data?

  • yanivshe's avatar
    yanivshe
    6 years ago

    Managed to solve the issue, though now I have a new one...

    The problem was, that the source query send a datetime field.

    On the Power BI query editor, when I changed it to date field, for some reason the timestamp still affected the relationships between tables.

    I had to cast the fields from the source SQL query in order to get only the date.

10 Replies

  • Ani1991's avatar
    Ani1991
    Resolver III
    7 years ago

    Hi yanivshe 

    If you don't mind can you please share some sample data or screenshots of the same.

     

    Thanks,

    Ani

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    7 years ago

    Most likely the Datetypes dont match, and the relationship is not correctly defined.

     

  • yanivshe's avatar
    yanivshe
    Helper I
    7 years ago

    The tables are quite simple.

     

    Open_Dates: [Order_id<text>, open_date<date>]

    Close_Dates: [Order_id<text>, close_date<date>]

    Date_Table: [date<date>]

     

    The relations are on the date fields: {1:1)

    Open_Dates[open_date] <-> Date_Table[date]

    Close_Dates[open_date] <-> Date_Table[date]

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi yanivshe 

    Create relationship as below

    If there are many duplicated dates in one table, please use "many to one" relationship

     

    Then create measures in date table

    the ammount of open orders = COUNT('open'[order id])

the ammount of close orders = COUNT('close'[order id])

diff = [the ammount of open orders]-[the ammount of close orders]

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • yanivshe's avatar
      yanivshe
      Helper I
      6 years ago

      Hi,

       

      Sorry for the late reply.

       

      I tried your suggestion and it sounds good.

      The problem is that when I put the measures in a table not all dates appear :smileyfrustrated:


      Here is the model for "OPEN_DATES"


      Here is the model for "Dates" table: (marked as date table)
      Here is the relationship: (on the dates fields)

      In my first screenshot you can see that Many of the dates are blank.

      Any idea what is wrong here?

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi yanivshe 

        I can't reproduce your problem.

        Could you share a simple file(remove large data, just keep simple data what can show the problem)?

         

        Best Regards
        Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi yanivshe 

    Is this problem sloved? 

    If it is sloved, could you kindly accept it as a solution to close this case?

    If not, please let me know.

     

    Best Regards

    Maggie