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

Date Transformations in Direct Query mode?

Hello,

I have been trying "Direct Query" to our sql server tables all on 1 database, and it seems to work fine.

But now I bumped on limitations of Direct Query, when I tried to make transformations with Date. As I have understood, special treatment of date columns (year, quarter, month, day, so on) are still not supported in DirectQuery mode.

What can I do in this case?

I have a date "01.01.2015 00:00:00", but I need also columns with

  • MonthYear
  • Month(name)
  • Year

for example: Jan 2015; January; 2015

(actually I need also get rid of "00:00:00", but Direct Query prohibits this transformation as well)

 

Another question is, is it possible in Power BI Desktop - Direct Query Modus to work with calculated columns and measures, and DAX at all?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Date Transformations in Direct Query mode?

Hi @Jolyon,

In your scenario, please follow the steps below to create a column in Date table and use the column in visual filter.

1. Create a Column called MonthSequentialNumber in the date table using the following formula.
MonthSequentialNumber = MonthSequentialNumber = year('Date'[Date]) * 12 + Month( 'Date'[Date]) – 1

2. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

3. Create a column in Date table called show using the fomula below

show = IF('Date'[MonthSequentialNumber]>=[CurrentMonthSeqeuntialNumber]&& 'Date'[MonthSequentialNumber]<=[CurrentMonthSeqeuntialNumber]+5,1,0)

4. Show column will have a value of 0 or 1 in the date table.

5. Create relationship using Date columns in Date table and your target table.

6. Create a column chart where Axis is Date and Value is OpportunitiesAmount, then drag Show column to visual filter, there is an example for your reference, for more details, please review the example in this attached PBIX file.
Capture.PNG

 
Reference:
http://community.powerbi.com/t5/Desktop/Filter-Dates-to-only-show-current-month-and-next-12-months/t...



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.
13 REPLIES 13
Super User
Super User

Re: Date Transformations in Direct Query mode?

Yes, you can work with calculated columns and measures with Direct Query now. I would recommend turning on File | Options and settings | Options | Direct Query | Allow unrestricted measures in Direct Query mode.

 

Here is the latest documentation on what works with Direct Query (although this is for SQL Server 2016 so your mileage may vary):

https://msdn.microsoft.com/en-us/library/mt723603.aspx

 

Although, now that I look at this, these are about analysis services and not database services so now I'm going to have to test and make sure this stuff still holds.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Date Transformations in Direct Query mode?

OK, I did confirm that in Direct Query mode against a database, I can add custom columns in M, calculated columns in Desktop and measures in Desktop.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jolyon Member
Member

Re: Date Transformations in Direct Query mode?

Hi @Greg_Deckler,
thanks for the answer! I did as you told.
But still I can do only limited number of transformations with date.
E.g. I have originally date 01.12.2012.

I can transform it now to Year-> 2012 (I just make a duplicate of column with date "01.12.2012" and then chose Transform-->Year.

But I can not transform it to Month --> December.

 

I get the error message"This transformation is not supported by Direct Query mode".

 

Do you have any other idea, what could I do?

 

If this helps, a bit of information about using Power BI: I want to make some reports basing on data from our CRM System.

So we connected from Power BI Desktop (DirectQuery) through SQL Server to our CRM database.

Is there probably more exquisite way to get data and make reports?

Moderator v-yuezhe-msft
Moderator

Re: Date Transformations in Direct Query mode?

Hi @Jolyon,

You can use the following formulas to transform your date columns, also check the example in the screenshot below.

Year = YEAR('Sales SalesOrderDetail'[ModifiedDate])

Month = SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1,"Jan",2,"Feb",5,"May",6,"Jun",7,"Jul")

Yearmonth = CONCATENATE('Sales SalesOrderDetail'[Month], 'Sales SalesOrderDetail'[Year])

MonthName = SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1,"January",2,"Febuary",5,"May",6,"June",7,"July")

1.PNG


In addition, please click the date column in the Fields panel  in the following screenshots, then you can click on Modeling in the Desktop ribbon and change data type of the column to Date and change date format to your desired format.
2.PNG3.PNG


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

Re: Date Transformations in Direct Query mode?

Hi, @v-yuezhe-msft,

 

Thanks a lot for the answer! This could be a solution.

 

I have one more question: can I give for this formula only up to 10 Values? Because when I tried to give all the 12 Months in the formula like:

 

Month = SWITCH(MONTH('Table1'[Modified_date]); 1;"Jan"; 02;"Feb";3;"März";4;"April"; 5;"May";6;"Jun";7;"Jul";8;"August";9;"September";10;"Oktober";11;"November";12;"December")

 

I got an error message:

 screen3.png

 

Another general question: as I connected through DirectQuery-->SQL Server-->to our CRM database,

I have got the views only for Reports and Relations, but no Datasets:

 

 screen1.png

Is the only possible way to create measures and new columns(with formula) in this case - just create it directly in Reports view?

If I go to Query Edit, I find there only customized column, but no Measures or Columns.

 

Thanks a lot!

Super User
Super User

Re: Date Transformations in Direct Query mode?

Try nested IF instead of SWITCH.

 

Yes, when using Direct Query, then the only way to create measures and calculated columns is in Report View.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Moderator v-yuezhe-msft
Moderator

Re: Date Transformations in Direct Query mode?

 @Jolyon,

Use the following formula instead.

MonthName= IF(MONTH('Sales SalesOrderDetail'[ModifiedDate])<6,SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1, "January", 2, "February", 3, "March", 4, "April" , 5, "May"),SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),6, "June", 7, "July", 8, "August"
, 9, "September", 10, "October", 11, "November", 12, "December"
, "Unknown month number"))

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

Re: Date Transformations in Direct Query mode?

Hi @v-yuezhe-msft
Thank you for the answer, it solved a part of the problem!

I have one more question about Date Transformation:

If I need to show  the OpportunitiesAmount from NOW till next 6 Months(or say 180 days), how could I do that? is it possible to create such a Variable, that would define, what Date is it today and take also next 180 days?

 

("NOW" implying the day, when I open and refresh my BI Report(which uses DirectQuery))

Moderator v-yuezhe-msft
Moderator

Re: Date Transformations in Direct Query mode?

Hi @Jolyon,

In your scenario, please follow the steps below to create a column in Date table and use the column in visual filter.

1. Create a Column called MonthSequentialNumber in the date table using the following formula.
MonthSequentialNumber = MonthSequentialNumber = year('Date'[Date]) * 12 + Month( 'Date'[Date]) – 1

2. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

3. Create a column in Date table called show using the fomula below

show = IF('Date'[MonthSequentialNumber]>=[CurrentMonthSeqeuntialNumber]&& 'Date'[MonthSequentialNumber]<=[CurrentMonthSeqeuntialNumber]+5,1,0)

4. Show column will have a value of 0 or 1 in the date table.

5. Create relationship using Date columns in Date table and your target table.

6. Create a column chart where Axis is Date and Value is OpportunitiesAmount, then drag Show column to visual filter, there is an example for your reference, for more details, please review the example in this attached PBIX file.
Capture.PNG

 
Reference:
http://community.powerbi.com/t5/Desktop/Filter-Dates-to-only-show-current-month-and-next-12-months/t...



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.