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

[Jolyon]

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

Now I want to present Tickets A and B in bar diagramm, with date (MonthYear) as axis:

The problem is, that i have date in two different tables. Should I make a merged Table with tickets A/B and date to compare them?

-is it the only possible way?

-can I use merged tables in direct query mode?

-Or is it possible to present date as some universal table,which i can use for all the tables?

I am not too willing to use merged table, because two original tables A and B don't have the same structure(columns).

Thank you very much.

[BetterCallFrank]

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]

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]

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]

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]

No you can't. You have to do this table in your datasource. What datasource do you use? SSAS tabular?

Pavel

[Jolyon]

@PavelR I get data from CRM system.

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

thank you and regards,
Jolyon

[v-yuezhe-msft · Accepted solution]

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