Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
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
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 I get data from CRM system.
does it mean, we need to creat new entity table within CRM?
thank you and regards,
Jolyon
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
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |