Can not New Table or use DAX function in DirectQuery mode

[phuongui]

Dear all,

I have a problem when use DirectQuery mode to get SQL Server Database. I turned on "Allow unrestricted measures in DirectQuery mode" but it can use another features such as, New Table or DAX function.

How can I fix this problem?

Thanks,

Phuong

[prateekraina · Accepted solution]

Hi @phuongui,

Creating a new table is not allowed in Direct Query even if you Allow Restricted stuff.

I faced the same problem at my end but since i had to create a small table of 7 rows and 2 columns, i did the following:

1. In Query Editor Section, right click on your any of the existing table and click on duplicate.
2. Rename it, Now in PowerQuery section, remove the steps, if any, after Source.
3. Edit source and wriite a custom SQL query to create your own table.

Note: By creating table i mean, use SELECT and UNION ALL to create your table.
Eg:

SELECT 1 AS [StudentID]
,'Ryan' AS [StudentName]

UNION ALL

SELECT 2 AS [StudentID]
,'David' AS [StudentName]

This approach can be useful if you have to create small table and when you can't afford to switch from DirectQuery mode to other.

Thanks !!

[prateekraina · Accepted solution]

Hi @phuongui,

Creating a new table is not allowed in Direct Query even if you Allow Restricted stuff.

I faced the same problem at my end but since i had to create a small table of 7 rows and 2 columns, i did the following:

1. In Query Editor Section, right click on your any of the existing table and click on duplicate.
2. Rename it, Now in PowerQuery section, remove the steps, if any, after Source.
3. Edit source and wriite a custom SQL query to create your own table.

Note: By creating table i mean, use SELECT and UNION ALL to create your table.
Eg:

SELECT 1 AS [StudentID]
,'Ryan' AS [StudentName]

UNION ALL

SELECT 2 AS [StudentID]
,'David' AS [StudentName]

This approach can be useful if you have to create small table and when you can't afford to switch from DirectQuery mode to other.

Thanks !!

[phuongui]

But why I can not type DAX function in DirectQuery mode? I choose New Colume, just get month from date column with FORMAT keyword, it doesn't work. Maybe create new table is not necessary, even when DAX function is unavailable too... 😞

[prateekraina · Accepted solution]

Hi @phuongui,

As far as i know, FORMAT is not allowed while creating a 'Column' in DAX. It can be used while creating a 'Measure'
Nevertheless, if you just want to take month from Date colulm, use below DAX, it will work for sure:

MonthColumn = MONTH([Date])

Thanks !!

[phuongui]

Ohh :D, thank you so much

[prateekraina]

Most Welcome 🙂
If you think i have solved your issue, kindly accept it as a solution.

Thanks !!

[phuongui]

@prateekraina

How to return month in 2 digits likes 01, 02...11, 12?

[prateekraina]

Hi @phuongui,

Sorry!! no clue about that,  even i searched for this solution as i wanted to sort my month names based on month numbers but due to this October being "10" always used to come after January being "1" :D.

But then i used MonthYear for sorting. Are you facing the same issue for sorting or you want to do something else?

[phuongui]

I'm facing with YearMonth I converted in SQL Server, it's formatted as "YYYYmm" (201705). But using Month([Date]) in Power BI just convert to 5 😄 and my result when concate with year is 20175, different from data in Database (201705)....

[prateekraina]

Try this in SQL to align with Power BI:

SELECT CONCAT(DATEPART(YEAR,[TimeStamp]) , DATEPART(MONTH,[TimeStamp])) AS [YearMonth]
FROM [dbo].[Table]

It will give results like below:

201610
201611
201612
20171
20172
20173
20174
20175

[phuongui]

Yeah, thank you very much 😄

[vanessafvg]

convert to them to text first, then concatenate, then change to integer, if you have an integer it will drop the preceding 0's

[vanessafvg]

as far as i know this is not a bug but the nature of using direct query, if you want to create tables etc you need to use imported mode.

[phuongui]

Yes, in Imported mode everything is ok. But when I use DirectQuery mode and Allow unrestricted measures but it's not work...

[vanessafvg]

correct, i think direct query mode expect you to have done most of the modelling in your source - so you will either need to push back the requirement to your source or swap to imported mode.