cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jolyon Member
Member

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

 

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

2.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Highlighted

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

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

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

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 Established Member
Established Member

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

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

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

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?
3.png

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

PavelR Established Member
Established Member

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

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

Pavel
Jolyon Member
Member

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

@PavelR I get data from CRM system.

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

 

thank you and regards,
Jolyon

Moderator v-yuezhe-msft
Moderator

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

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

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