Forum Discussion

thmonte's avatar
thmonte
Helper IV
9 years ago
Solved

Using Date to filter over multiple tables

I have multiple tables that all have date/time values for each row in the table.  I am looking to create a report and would like to be able to filter over date/time but I do not have a table of unique values to create a relationship from.  In addition I am using DirectQuery which does not allow me to create a summarized table of unique values.  Can anyone point me in the right direction?

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    thmonte

     

    You can make a time dimension without using summarize. Try making a new table with this:

     

    TimeDim = CALENDAR(FIRSTDATE(Table[Date]);LASTDATE(Table[Date])).

     

    Then you can link your tables to this new table, and slice on that. Let me know how it goes.

     

    Best,

    Martin

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    thmonte

     

    You can make a time dimension without using summarize. Try making a new table with this:

     

    TimeDim = CALENDAR(FIRSTDATE(Table[Date]);LASTDATE(Table[Date])).

     

    Then you can link your tables to this new table, and slice on that. Let me know how it goes.

     

    Best,

    Martin

    • thmonte's avatar
      thmonte
      Helper IV
      9 years ago

       

      Since I am using DirectQuery I am uanble to create a new table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        thmonte,

        You would need to create the summarized table that contains unique values in your source, and import the table to Power BI Desktop, then link your tables to the new table.


        Regards,
        Lydia

  • RR010's avatar
    RR010
    Regular Visitor
    9 years ago

    Hi Guys,

    I've got a related question. I made a Date table which is working (well.. for most of the time) but I still have a problem. I'll post a print screen as well to show what is happening.
    I have two tables, Actual Calls and Oppertunities, between these two I have small a Sales Rep table and a Date Table. When I make a report and filter bij Sales Rep it all works fine, when I try to filter by Date (the made date table) my Actual calls do filter but my Oppertunities dont.. I have no idea why..

    Hope you can help! Thank you!