Need help creating number of week

[pstanek]

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.

[sixtoquiles]

HI,

Any workaround to use WeekNum in direct query?

[alme]

No news? Have the same problem

[v-sihou-msft]

 @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. 

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

Regards,

[elliotdixon]

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.

Cheers,

ED.

[pstanek]

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.

[ankitpatira]

@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.