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?

Need Help

Anonymous
9 years ago
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.

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

Thanks,
Lydia Zhang

13 Replies

Greg_Deckler
Community Champion
9 years ago
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.
- Greg_Deckler
  Community Champion
  9 years ago
  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.
  - Jolyon
    Helper III
    9 years ago
    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?
Anonymous
Not applicable
9 years ago
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")

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.

Thanks,
Lydia Zhang
- Jolyon
  Helper III
  9 years ago
  Hi, Anonymous,
  
  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:
  
  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:
  
  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!
  - Anonymous
    Not applicable
    9 years ago
    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
- FrankWeng
  Frequent Visitor
  8 years ago
  Hi Lydia Zhang:
  Same question, how to transform to week of year format?
  Thanks for answer.