Forum Discussion

Jolyon's avatar
Jolyon
Helper III
9 years ago
Solved

how to use date form different tables (Direct Query)?

Hi all, need your help please. I have two tables,which are drawn with direct query to Power BI: Table 1 with columns Tickets Firm A, date and etc. Table 2 with Tickets Firm B, date and other colu...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Jolyon,

    Yes. You would need to create the new table in CRM, as we are not able to use "New table" feature in DirectQuery Mode.

    Thanks,
    Lydia Zhang

BetterCallFrank's avatar
BetterCallFrank
Resolver IV
9 years ago

Hi Jolyon

 

use can do this:

 

1) create date table (one row for each day), look at CALENDAR DAX function

2) create relationship from both tables A and B to date table

3) add date axis to visual

4) add measures from A and measures from B to visual

 

no merged table needed :-)

 

HTH,

Frank

  • Jolyon's avatar
    Jolyon
    Helper III
    9 years ago

    Hi BetterCallFrank
    thanks for the answer. But i have then another question:

     

    since I use direct query, I can not create new table in this mode - main problem.

    Besides, if I use CALENDAR function, i should know start and end date in advance,is it correct?what if i cannot know, which table contains start date and end date?or it may change from time to time.

    ( this is only  example of CALENDAR function,that I found:
    =CALENDAR (DATE (2005, 1, 1), DATE (2015, 12, 31))

    or

    =CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date])))

     

    Hi PavelR  do you have probably any idea about this problem?

     

    Regards,

    Jolyon

    • PavelR's avatar
      PavelR
      Solution Specialist
      9 years ago

      Hi Jolyon,

       

      I agree with solution described by BetterCallFrank.

       

      To solve CALENDAR function parameters, just include IF formula condition to input MIN and MAX dates from both table. Something like depicted below:

       

      DateTable = CALENDAR(IF(MIN(Table2[Column1])>MIN(Table1[Column1]);MIN(Table1[Column1]);MIN(Table2[Column1]));IF(MAX(Table2[Column1])>MAX(Table1[Column1]);MAX(Table2[Column1]);MAX(Table1[Column1])))

      Regards.

      Pavel

       

       

      • Jolyon's avatar
        Jolyon
        Helper III
        9 years ago

        hi PavelR thank you,
        and what about direct query?

        I get 2 tables with direct query and in this mode i didn't find, how to create a new table. I think,it is impossible?

        A new measure and new column creation is possible, but not a new table..