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

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..

  • PavelR's avatar
    PavelR
    Solution Specialist
    9 years ago
    No you can't. You have to do this table in your datasource. What datasource do you use? SSAS tabular?

    Pavel
    • Jolyon's avatar
      Jolyon
      Helper III
      9 years ago

      PavelR I get data from CRM system.

      does it mean, we need to creat new entity table within CRM?

       

      thank you and regards,
      Jolyon

      • Anonymous's avatar
        Anonymous
        Not applicable
        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