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

Need help creating number of week

Hi I use direct query within Power BI  and WEEKNUM doesn´t work because Calculated column dax formrmula doesn´t support WEEKNUM in direct query. How can I calculate it within direct Query?

 

I have column with dates and I need to add column with week number.

6 REPLIES 6
Super User
Super User

Re: Need help creating number of week

@pstanek I cannot replicate your issue. I have directquery to sql server and can successfully create calc column with WEEKNUM function. Ensure you have latest version of pbi desktop and go to File -> Options and Settings -> Options and check that you have allow unrestricted measure ticked.

 

Capture.PNG

Moderator v-sihou-msft
Moderator

Re: Need help creating number of week

 @pstanek

 

I reproduced same issue as yours, my power bi desktop version is 2.36.4434.381. Even I enable "unrestricted measures" in DirectQuery Option, I still get "not supported" error when adding a calculated column to get the week number from SQL table date column. 

Capture001.PNG

Capture00.PNG

I suggest you use DATEPART() get the week number on T-SQL query level. 

 

Regards,

pstanek Member
Member

Re: Need help creating number of week

Where can I find T SQL level?

 

File -> Options and Settings -> Options and check that you have allow unrestricted measure ticked.

Is this safe? I am afraid of incorrect results.

 

elliotdixon Member
Member

Re: Need help creating number of week

Hi @v-sihou-msft how did you get on with finding a solution to this? I have allowed the unrestricted measures but still cannot get the WEEKNUM working on Direct Query mode.

 

 

2016-09-07 11_28_10-Ernslaw DQ - Power BI Desktop.png

Cheers,

ED.

Highlighted
sixtoquiles Regular Visitor
Regular Visitor

Re: Need help creating number of week

HI,

 

Any workaround to use WeekNum in direct query?

alme Frequent Visitor
Frequent Visitor

Re: Need help creating number of week

No news? Have the same problem